Stored procedure - Only return if rows > 0

Thomas

9y1.1kSQL

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhilash J A9y ago
Accepted 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.
0
Abhilash J A9y ago
You can also execute and evaluate the results using the If exists strategy. IF EXISTS(exec(@query)) BEGIN exec(@query) END
0
Thomas9y ago
I should note that every executed query returns a different set of columns/fields, so i can not use a temp db
0
Abhilash J A9y ago
Does each query return the same fields in the its results?
0
Abhilash J A9y ago
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).
0
Rajkiran Swain9y ago
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure
0
Nilesh Shah9y ago
You can get the result into some temp table, then check with if exist clause, if any record exist then return result
0