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

Question

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.

Answer 1

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]

Answer 2

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()