CODEWITHSUNDEEP
sql-serverstored-procedures
Mike
Mike

Reputation: 107

Counting row multiple exec stored procedure

I have stored procedure which calling another procedure something like this

CREATE PROCEDURE [dbo].[TEST_LOOPING]
  @ID nvarchar(50) = null
AS
BEGIN
    exec proc_1 @ID
    exec proc_2 @ID
    exec proc_3 @ID
END

can I get total row result from test looping? and show it as additional select in the end not as output

NB : can the result shown at first row? like this

Total
275
result for proc_1
result for proc_2
result fro proc_3

Upvotes: 1

Views: 464

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

CREATE PROCEDURE [dbo].[TEST_LOOPING]
  @ID nvarchar(50) = null
AS
BEGIN
    DECLARE @c INT = 0;

    exec proc_1 @ID
    SET @c += @@ROWCOUNT;
    exec proc_2 @ID
    SET @c += @@ROWCOUNT;
    exec proc_3 @ID
    SET @c += @@ROWCOUNT;

    SELECT @c AS Total
END

EDIT:

I don't recommend using it, but it is possible:

CREATE PROCEDURE [dbo].[TEST_LOOPING]
      @ID nvarchar(50) = null
    AS
BEGIN
    DECLARE @c INT = 0;


    CREATE TABLE #temp1(cols... )
    CREATE TABLE #temp3(cols... )
    CREATE TABLE #temp3(cols... )

    INSERT INTO #temp1(cols ...)
    exec proc_1 @ID
    SET @c += @@ROWCOUNT;

    INSERT INTO #temp2(cols ...)
    exec proc_2 @ID
    SET @c += @@ROWCOUNT;

    INSERT INTO #temp3(cols ...)
    exec proc_3 @ID
    SET @c += @@ROWCOUNT;

    SELECT @c AS Total

    SELECT *
    FROM #temp1

    SELECT *
    FROM #temp2

    SELECT *
    FROM #temp3
END

Another solution as Gordon Linoff suggest is to modify each stored procedure to return records number in OUTPUT parameter, but even then if you want total record number as first result set them you need to store data in intermediate temp tables.

Upvotes: 2

Related Questions