stored procedures - Removing empty tables from TSQL sproc output data sets? -

stored procedures - Removing empty tables from TSQL sproc output data sets? -

i have tsql sproc 3 loops in order find relevant data. if first loop renders no results, sec 1 does. append table has multiple values can utilize later on.

so @ should have 2 tables returned in dataset sproc.

the issue if first loop blank, end 3 info tables in info set.

in c# code, can remove empty table, rather not have returned @ sproc.

is there way remove empty table within sproc, given following:

exec (@sqltop + @sqlbody + @sqlbottom) set @numberofresultsreturned = @@rowcount; . . . if @numberofresultsreturned = 0 begin set @searchloopcount = @searchloopcount + 1 end else begin -- have data, no need run 1 time again break end

the process goes follows: on first loop there no results. rowcount 0 because exec executes dynamically created sql query. that's 1 table.

in next iteration, results returned, making 2 info tables in dataset output, plus 3rd 1 added on end.

i didn't want count(*) if > 0 perform query want minimize queries.

thanks.

you can set result sp in table variable , check if table variable has info in it.

something sp named getdata returns 1 integer column.

declare @t table(id int) declare @sql varchar(25) -- create dynamic sql set @sql = 'select 1' -- insert result @sql @t insert @t exec (@sql) -- check info if not exists(select * @t) begin -- no info go on loop set @searchloopcount = @searchloopcount + 1 end else begin -- have info wee need query info select * @t -- terminate loop break end

tsql stored-procedures c#-4.0