Stored procedure - Only return if rows 0

Question

Hi all. I have this stored procedure which executes a lot of sql queries and returns the results. How do i go about returning only the results from the queries that returns rows? BEGIN DECLARE @Query varchar(4000) DECLARE cur CURSOR FOR SELECT SQLSyntax FROM tblChecks OPEN cur FETCH NEXT FROM cur INTO @Query WHILE @@FETCH_STATUS = 0 BEGIN EXEC (@Query) FETCH NEXT FROM cur INTO @Query END CLOSE cur DEALLOCATE cur END

Mani Kandan · Answer

To avoid returning empty result sets, you have to conditionally run each query. For instance: declare @query varchar (4000) declare cur cursor local for select 'if exists (' + SQLSyntax + ') ' + SQLSyntax from tblChecks open cur fetch next from cur into @query while @@fetch_status = 0 begin exec (@query) fetch next from cur into @query end close cur deallocate cur Or, if every result set is identical, you could create a temp table first, and then insert all values into the temp table, and then do a single select statement at the end from the temp table: create # temp (field1 type null , field2 type null , field3 type null ) declare @query varchar (4000) declare cur cursor local for select 'insert into #temp ' + SQLSyntax from tblChecks open cur fetch next from cur into @query while @@fetch_status = 0 begin exec (@query) fetch next from cur into @query end close cur deallocate cur select * from # temp drop table # temp If possible, the 2nd option is better, because it will only run each query one time. The 1st option will run each query twice (that returns data), once to test if there are any results and once to return the data. This is not efficient! Hopefully all queries return the same fields in the results and you can use the 2nd option.

Nilesh Shah · Answer

You can get the result into some temp table, then check with if exist clause, if any record exist then return result

Mani Kandan · Answer

You can also execute and evaluate the results using the If exists strategy. IF EXISTS(exec(@query)) BEGIN exec(@query) END

Thomas · Answer

I should note that every executed query returns a different set of columns/fields, so i can not use a temp db

Mani Kandan · Answer

Does each query return the same fields in the its results?

Mani Kandan · Answer

Why not have the application properly discard resultsets where rowcount = 0? SQL Server doesn't know in advance that some dynamic SQL you're about to throw at it will yield 0 or > 0 rows. So the only way to do that, really, would be to collect all of the information in a #temp table and return it as one (or loop over it again), or execute each query in the loop twice (once to check for existence, then maybe once more if the count is > 0).

Rajkiran Swain · Answer

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure

Stored procedure - Only return if rows > 0

Answers (7)