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.

Alboran

You need to execute that SQL statement and see if it gives you any results... – Typhon

You're going to have to run some SQL query to determine if there's any rows... – Stain

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]

Rondarondeau

@CraigHB - Good! Hopefully the queries are all predefined and not in any way from user input because of obvious SQL injection possibilities. – Rondarondeau

Yes, predefined and inside a proc. – Alboran

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

Halflife

You can do this. EXISTS is more efficient though as it can stop after the first matching row instead of counting them all. – Rondarondeau

@Martin: Agreed Exists IS more efficient. But I was thinking about the query, having more than single select statement. – Halflife

Answer 3

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.

Homophonic

Ah, I see too. Original Q was based on '[not] having to actually run the script' – Homophonic