Total number of Result Sets in Stored Procedure

Question

In SQL Server 2008, is there any way to get the total numbers of Result sets (tables) been populated after an execution of stored procedure.

Lets say I have one stored procedures which internally calls another stored procedure. I want to know that how many result sets it returns that internally called stored procedure.

Can anybody can assist me on this.

e.g.

CREATE PROCEDURE sp_GetReports
(
    @reportName AS VARCHAR(50)
)
AS
BEGIN
    DECLARE @reportProcName AS VARCHAR(50)

    SELECT  @reportProcName = ReportProcName
    FROM ReportMaster
    WHERE ReportName = @reportName;

    EXEC (@reportProcName)

    /*
    * Need to get here, total numbers of Result Sets (tables) retrived.
    */


END
GO

Thanks in advance.

i doubt this is possible. Why does your stored procedure returns multiple result set ? It might not be easily consume by the front end application. — Squirrel, Feb 26 '16 at 09:33
In the dynamic stored procedure as per report requirement there are multiple results set are returning — Bhavesh Harsora, Feb 26 '16 at 09:46
This has nothing to do with your actual question but you should avoid the `sp_` prefix on your stored procedures. See [here](http://stackoverflow.com/a/20530262/3959259) for more details. — Bill Tür stands with Ukraine, Feb 26 '16 at 10:10
Thomas, The written Procedure name is just for example, the actual procedure name is different rather. — Bhavesh Harsora, Feb 26 '16 at 10:18
What is your client that is calling this sproc? in .net you can use Reader.NextResult(); this will handle multiple result sets and you can count them there. Alternatively If you have access to the source code of the sprocs then you can maintain a counter of result sets that you produce in your code and pass this to each caller. Alternatively you could use SET SHOWPLAN_ALL and run the query once and do a dry run, check how many selects there are, then turn that off and rerun so you get the output — cameront, Feb 26 '16 at 11:32
As there are large no. of procedures in the ReportMaster Table which may come into execution, So am not think a good solutions to get it dry run each and every procedures and get the count. even don't know this may be a ultimate solution. — Bhavesh Harsora, Feb 26 '16 at 12:04

Tom · Answer 1 · 2019-09-15T15:20:49.210

You might be able to:

Get the row count in the stored procedure and retrieve the result as an OUTPUT parameter
Utilize some type of external table (would have to design this)

This gives you the number of rows returned from the stored procedure

USE Northwind
GO

CREATE OR ALTER PROCEDURE dbo.Test (
    @rowCnt int OUTPUT
)
AS

SELECT * FROM dbo.Customers
SET @rowCnt = @@ROWCOUNT
GO

DECLARE 
    @rowCount int

EXEC dbo.Test @rowCount OUTPUT

SELECT @rowCount

score 0 · Answer 2 · answered Mar 14 '17 at 13:53

0

What about a workaround?

Create some counter variable in each report. Every time before SELECT statement, increment it. Then, return it as the last output in sproc. Each report should follow this convention.

answered Mar 14 '17 at 13:53

Orchidoris

188
7

score -1 · Answer 3 · answered Feb 29 '16 at 08:24

-1

You can return an output parameter with @@ROWCOUNT from inner procedure and use it in sp_GetReports.

answered Feb 29 '16 at 08:24

Helio

621
1
4
24

@@ROWCOUNT returns number of Row affected by lastly executed tsql, I need Table count to be returned by last one executed stored proc. So this @@ROWCOUNT isn't help me in this regards. – Bhavesh Harsora Mar 01 '16 at 10:24

Total number of Result Sets in Stored Procedure

3 Answers3