How to get output of one Stored Procedure into another

Sharing data between two stored procedures

How to return a status code from stored procedure
Using Output parameters in stored procedure
Use temporary table populated by inner stored procedure

1. How to return a status code from SP

In this case, we can simply 'return' some value, which can be some system defined status flags or you can return your own value.

--# SP, which returns 100 on some error condition otherwise 0
create proc sp_returnCode
as
... some processing ....
If ( ...some error condition... )
return 100 --#Your custom return code
else
return 0 --#Success code

This SP can be called by another SP or query, to collect this return status as below

declare @status int
execute @status = sp_returnCode
select @status

Note that, only one value can be "returned"

Sybase ASE does reserve 0 to indicate 'success' and -1 to -99 return codes to indicate different errors . For your custom return codes use other than this range.

2. Using Output parameters in SP

This is another way to return values from one SP to another SP or query .
Output parameters are defined like input parameters.

--#SP to multiply two INPUT parameters and return the result in OUTPUT parameter.

create proc sp_multiply
( @para_1 int, @para_2 int, @result int OUTPUT )
as
select @result = @para1 * @para2

--#Collect the output result
declare @result int exec sp_multiply 7, 3, @result OUTPUT

Unlike 'return code', you can return multiple OUTPUT parameters
Lets update above SP, to have a additional OUTPUT parameter, say addition of two numbers.

create proc sp_multiplyAdd
( @para_1 int, @para_2 int, @result_1 int OUTPUT, @result_2 int OUTPUT )
as
select @result_1 = @para1 * @para2
select @result_2 = @para1 + @para2

You can access the two OUTPUT variables of the stored procedure as below,

declare @result_1 int
declare @result_2 int
exec sp_multiplyAdd 7, 3, @result_1 OUTPUT , @result_2 OUTPUT
select @result_1 , @result_2

3. Sharing temp table between two Stored Procedures

If you want to use a result set from one stored procedure into another, temporary tables can be a good option.

Basically, we create a temp. table which is populated by first SP, and the same temp. table is then used by another stored procedure.

--#Create a temp. table, with same definition as that of the result set.
create table #shareMe ( col_1 int, col_2 char(9), ...... )

--#Create a SP to populate this table with the SP result set as below
create proc sp_populateResult
as
insert into #shareMe
select * from TAB_A where col_1 = @someValue

Note : Once this SP is compiled you can drop the temp table, as we will be creating it in calling SP.

Now you can access this temp table in another stored procedure.

create proc sp_consumeResult
as
BEGIN
--#Define the temp. table
select * into #shareMe from TAB_A where 1 = 2

--#Execute the SP which populates the result set.
exec sp_populateResult

--#Access temp table
select * from #shareMe

END