How to tell if SQL stored in a variable will return any rows

If I have a SQL script stored in a variable like this:

DECLARE @SQL VARCHAR(MAX) = 'SELECT * FROM Employees WHERE Age > 80'

How can I tell if @SQL would return any rows if I were to run it?

In effect this:

IF EXISTS(@SQL) print 'yes, there are rows' --开发者_运维技巧 Dummy code -- does not work!

I would like to try this without having to run the script in @SQL, insert that into a table and them count the rows.

Of course you need to run the script. To avoid having to insert the result into a table and count the rows you can use sp_executesql and an output parameter.

DECLARE @Statement NVARCHAR(MAX) = 'SELECT * FROM Employees WHERE Age > 80'

DECLARE @DynSQL NVARCHAR(max) = N'SET @Exists = CASE WHEN EXISTS(' + 
                                @Statement + 
                                N') THEN 1 ELSE 0 END'

DECLARE @Exists bit
EXEC sp_executesql @DynSQL,
                   N'@Exists bit OUTPUT',
                   @Exists = @Exists OUTPUT

SELECT @Exists AS [Exists]

While Martin's answer is also valid but can't we just use the @@RowCount after Executing the script? like

DECLARE @q nvarchar(max);
SET @q = 'declare @b int; select * from sys.tables where @b = 5';

EXEC (@q);

If @@RowCount > 0
    Print 'Rows > 0';
Else
    Print 'Rows = 0';

Note that the query has a variable declaration in it, which obviously cannot be used with Exists()

You can try an out-of-the-box solution.

For example. Keep track of a single variable called emp_over_80. Whenever you add an employee over that age, emp_over_80++. When you remove one, emp_over_80--

At the beginning of each day, run a query to determine the value of emp_over_80 (it may be an employee's birthday). Then, throughout the day, you can refer to emp_over_80 instead of re-running the SQL query.

Other options would be to keep the employee table sorted by age. If the last employee is over 80, then your query will return at least one row.

Now, many might say these are horrible coding practices, and I'd agree with them. But, I don't see another way to magically know the result (even a partial result) of a query before it runs.