Calling Procedure result set (Out Put) in another procedure

One of my friend asked me about calling a procedure result set into another procedure to perform some further operations before final result. Using this technique we can use procedure as a function.
I have 2 solutions for that


1- Using Table Parameter in procedure to get table data and perform some actions on that. Table Value Parameter. This can be used in SQL SERVER 2008 and above only.
2- Calling procedure into another procedure and storing its data into temporary table.


I am going to mention an example for second solution and link for another post to understand second one.


In first picture I have created a procedure that create a declare table insert some data into that table.

In second picture I have created another procedure that create a declare table  . Note there is an Insert statement for second table but this insert statement using an result data set from first procedure.

In last picture there are 2 execute statements for both first and second procedure. You can clearly view that first result set is the output from first procedure and in 2nd result set i have add some more rows along with first procedure output that is already added in second table using exec procedure statement. Now in my last output i have used Rollup statement to sum up total values. that you can see in last result set.

Using this technique you can use existing procedure result set to add some more logic.

Here is the code for this example.



if exists (select * from sys.objects where name like 'up_returnDataSet')
drop procedure up_returnDataSet
go
if exists (select * from sys.objects where name like 'up_returnDataSet2')
drop procedure up_returnDataSet2
go


Create procedure up_returnDataSet
as
begin
Declare @vTable Table(ID int,Value int)

Insert into @vTable values (1,10),(1,50),(2,15),(2,45),(3,30),(3,10)

Select * from @vTable

end

go

Create procedure up_returnDataSet2
as
begin
Declare @vTableInsert Table(ID int,Value int)

Insert into @vTableInsert
exec up_returnDataSet

-- Insert some more values
Insert into @vTableInsert values (1,55),(2,65),(3,95)

Select ID,Value from @vTableInsert order by id

-- Return Totals with New values
Select isnull('Category Total:' +cast(ID as varchar(5))  ,'Grand Total'),SUM(Value) from @vTableInsert
group by ID
with ROLLUP

end


-- displaying results
exec up_returnDataSet
exec up_returnDataSet2