Only return recordset(s) from Stored Procedure if it has rows

Question

I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty.

I'd like to reduce overhead and only return those which have 1 or more rows.

My question is - How can I only return those recordsets which have rows?

The application simply expects 0 or more recordsets, and loops through each, and prints them out.

I know I can skip them in the application code, but I am trying to prevent them from being returned at all, if empty.

The procedure is as simple as this:

CREATE PROCEDURE bfsp_PROC_NM
AS 

    BEGIN

        SELECT * FROM TABLE_1

        SELECT * FROM TABLE_2

        SELECT * FROM TABLE_3

        RETURN  

    END
GO

In the actual procedure, some of the queries are expensive, so I don't want to have to test the query, then if it returns a row or more, execute it again... as it would be too expensive.

Answer 1

I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty.

I'd like to reduce overhead and only return those which have 1 or more rows.

That's a terrible idea. How is the application going to know what resultset it is processing? Stored procedures should have a fixed set of resultset shapes.

That being said, to do this load the results into temp tables, and then SELECT from those if they are non-empty.

eg

CREATE PROCEDURE bfsp_PROC_NM
AS 

    BEGIN
        SELECT * 
        into #result1
        FROM TABLE_1
        if @@rowcount > 0
          select * from #result1

        SELECT * 
        into #result2
        FROM TABLE_2
        if @@rowcount > 0
          select * from #result2

        SELECT * 
        into #result3
        FROM TABLE_3
        if @@rowcount > 0
          select * from #result3

    END
GO

Answer 2

I like my code to be predictable, if you always return X sets of data, you can program that in your application, and you always know what to expect. So perhaps it's not the best idea to filter out sets.

However, an option could be to do a check if the table/select you're executing returns rows, if they do you return the set, otherwise you skip it. This obviously has a bit of overhead since you have to specify it for every result set you're returning.

Example as seen in this code:

CREATE TABLE dbo.ReturnSet1( Id INT IDENTITY,NickName VARCHAR(256) )
CREATE TABLE dbo.ReturnSet2 (Id INT IDENTITY,NickName VARCHAR(256) )

INSERT INTO ReturnSet1
VALUES('TestRecord')

GO
CREATE PROCEDURE dbo.TestReturnSet
AS
BEGIN

    IF EXISTS ( SELECT 1 FROM dbo.ReturnSet1 )
    BEGIN
        SELECT NickName
         FROM dbo.ReturnSet1
    END

    IF EXISTS ( SELECT 1 FROM dbo.ReturnSet2 )
    BEGIN
        SELECT NickName
         FROM dbo.ReturnSet2
    END


END
GO

EXEC dbo.TestReturnSet

DROP TABLE dbo.ReturnSet1
DROP TABLE dbo.ReturnSet2
DROP PROCEDURE dbo.TestReturnSet

Answer 3

Assuming that data from all three tables has similar structure you could insert the data into a single temporary table and then have your procedure return one result set with all the available rows:

create procedure one_result_set

    # create temp table to hold all result rows
    create temporary table temp_one {
        table_name char(50),  # this field indicates source table
        field1 type1,
        ...
        fieldN typeN
    }

    # insert rows from first table into temp table 
    insert into temp_one
    select 'table1', t.* from table1 t;

    # insert rows from second table into temp table 
    insert into temp_one
    select 'table2', t.* from table2 t;

    # insert rows from third table into temp table 
    insert into temp_one
    select 'table3', t.* from table3 t;

    # finally, return all the fetched rows as a single result set
    select * from temp_one;

end procedure$$

The result set returned could still be empty - if none of the three source tables had any data to return - but you could determine this from the meta-data.

If the source tables do not share similar structure, then I have to agree with the previous responses - returning a variable number of result sets is a bad idea. If the source tables are different and your procedure returns fewer than three result sets then you will need to check the field list for each result set just to figure out what was missing before you can process the data.