Removing empty tables from TSQL sproc output data sets?

I have a TSQL sproc that does three loops in order to find relevant data. If the first loop renders no results, then the second one normally does. I append another table that has multiple values that I can use later on.

So at most I should only have two tables returned in the dataset from the sproc.

The issue is that if the first loop is blank, I then end up with three data tables in my data set.

In my C# code, I can remove this empty table, but would rather not have it returned at all from the sproc.

Is there a way to remove the empty table from within the sproc, given th开发者_Python百科e following:

EXEC (@sqlTop + @sqlBody + @sqlBottom)
SET @NumberOfResultsReturned = @@ROWCOUNT;
.
.
.
IF @NumberOfResultsReturned = 0
    BEGIN
        SET @searchLoopCount = @searchLoopCount + 1
    END
ELSE
    BEGIN
        -- we have data, so no need to run again
        BREAK
    END

The process goes as follows: On the first loop there could be no results. Thus the rowcount will be zero because the EXEC executes a dynamically created SQL query. That's one table.

In the next iteration, results are returned, making that two data tables in the dataset output, plus my third one added on the end.

I didn't want to do a COUNT(*) then if > 0 then perform the query as I want to minimize the queries.

Thanks.

You can put the result for your SP in a table variable and then check if the table variable has any data in it.

Something like this with a SP named GetData that returns one integer column.

declare @T table(ID int)
declare @SQL varchar(25)

-- Create dynamic SQL
set @SQL = 'select 1'

-- Insert result from @SQL to @T
insert into @T
exec (@SQL)

-- Check for data
if not exists(select * from @T)
begin
  -- No data continue loop
  set @searchLoopCount = @searchLoopCount + 1
end
else
begin
  -- Have data so wee need to query the data
  select *
  from @T

  -- Terminate loop
  break
end