Home Store the multiple result sets from stored procedures to different tables
Reply: 1

Store the multiple result sets from stored procedures to different tables

Piyusha Mohanty
1#
Piyusha Mohanty Published in 2017-12-07 04:15:26Z

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.

Jayasurya Satheesh
2#
Jayasurya Satheesh Reply to 2017-12-07 05:10:37Z

You can use the system variable @@ROWCOUNT to get the number of rows affected by your query.

The value of @@ROWCOUNT is updated with the number of rows affected by your last statement ( select / insert / update / delete ). so after the execution of your stored procedure, you can check the value in @@ROWCOUNT to get the number of rows returned by the procedure. Like this

declare @c int
exec OrderDetails
select @c =@@Rowcount

select @c

Here the no of rows returned by the procedure OrderDetails will be stored in a user-defined variable @c and you can use the same for any other operations.

Please refer this Link for more details on @@ROWCOUNT

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310195 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO