Store the multiple result sets from stored procedures to different tables

Question

I have around 30 to 40 stored procedures which have multiple result sets as output. My actual requirement is to count the number of rows from each of the result sets from respective procedures in SQL Server.

I can alter the procedures and use count() for each of the result sets. However, practically it's a tedious job to alter 30 to 40 procedures. Rather doing the same I was trying to use openquery process and store the output of procedures into some temp table.

However, in that way I'm only able to store the first result set, not any other later result sets. Even I was trying to achieve the same using cursors but doesn't work. Can you please help me some way?

Here is the code:

  create table Orders
   (
      Slno int,
      OrderID Char(20),
      NumberOfItem int,
      CustomerID int
    )

   Create table Customer
                 (
                   CustomerID int,
           CustomerName Char(30),
                   Address nChar(50)
                  )

   Insert into Customer values (1,'Piyu','Bhubaneswar'),
                         (2,'Ranu','Bhubaneswar'),
                         (3,'Tiku','Cuttack'),
                         (4,'Lui','Bhadrak'),
                         (5,'Wasim','Bhadrak'),
                         (6,'Vivek','Dhekanal'),
                         (7,'Romeo','Puri')

    Insert into Orders Values (1,'O1',2,1),
                              (2,'O2',7,1),
              (3,'O3',20,4),
              (4,'O4',2,3),
              (5,'O5',3,3),
                  (6,'O6',1,7),
              (7,'O7',1,5),
                      (8,'O8',3,1),
              (9,'O9',2,6)

    Select * from Orders
    Select * from Customer                 

    Alter Procedure OrderDetails
    @Var_TotalItems Int

    As
    Begin

        Select C.CustomerName,C.[Address ],O.NumberOfItem,O.OrderID   From 
        Customer C
        Join
        Orders O
        On C.CustomerID=O.CustomerID

        Select C.CustomerName,Sum(O.NumberOfItem) 
        [TotalNumberOfItem],Count(O.OrderID) [TotalNumberOfOrders]   From 
        Customer C
        Join
        Orders O
        On C.CustomerID=O.CustomerID
        Group by C.CustomerName
        Having Sum(O.NumberOfItem) >= @Var_TotalItems
    End

      EXEC OrderDetails 4

Here the procedures returns multiple outputs. My objective is to count the number of rows in each of the outputs without altering the stored procedure.

Answer 1

Simply I have taken the count from both the sql scripts and set it in two different variables. Then finally, added then and set the value in third variable.