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

Question

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

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

How can I find out if @SQL will return any rows if I have to run it?

In fact it is:

 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 it into the table and count the rows.

Answer 1

Of course you need to run the script. To avoid having to insert the result into the table and count the rows, you can use sp_executesql and the 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 use @@ RowCount after running the script? as

 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 request has a variable declaration in it, which obviously cannot be used with Exists ()

Answer 3

You can try the turnkey solution.

For example. Watch out for one variable called emp_over_80. Whenever you add an employee over this age, emp_over_80 ++. When you delete one, emp_over_80--

At the beginning of each day, run a query to determine the value of emp_over_80 (this may be the employee’s birthday). Then during 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 can say that these are terrible coding methods, and I agree with them. But I don’t see another way to find out the magic result (even partial result) of the request before it starts.