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
Create table Customer
Insert into Customer values (1,'Piyu','Bhubaneswar'),
Insert into Orders Values (1,'O1',2,1),
Select * from Orders
Select * from Customer
Alter Procedure OrderDetails
Select C.CustomerName,C.[Address ],O.NumberOfItem,O.OrderID From
[TotalNumberOfItem],Count(O.OrderID) [TotalNumberOfOrders] From
Group by C.CustomerName
Having Sum(O.NumberOfItem) >= @Var_TotalItems
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.