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

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.

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

StackExchange Database Administrators Q#253924, answer score: 5