I was wondering if it's possible to retrieve the results of a stored procedure using another stored procedure.
I have the following as a test example
The first stored procedure is intended to return two values
if(object_id('sp_function01')) is not null
drop procedure sp_function01
go
create procedure sp_function01
as
declare @lv_res01 char(20)
declare @lv_res02 char(20)
set @lv_res01 = 'fn01 result 01'
set @lv_res02 = 'fn01 result 02'
select @lv_res01, @lv_res02
go
Then the second is intended to retrieve those values and utilize them
if(object_id('sp_function02')) is not null
drop procedure sp_function02
go
create procedure sp_function02
as
declare @lv_var01 char(20)
declare @lv_res01 char(20)
declare @lv_res02 char(20)
set @lv_var01 = 'variable 01'
exec sp_function01
-- catch variables from this function
-- into lv_res01 and lv_res02
select @lv_var01, @lv_res01, @lv_res02
go
Yet the values for lv_res01 and lv_res02 are NULL and I'm not sure how to actually catch them
Something like this:
First procedure:
create procedure sp_function01
@lv_res01 char(20) OUTPUT,
@lv_res02 char(20) OUTPUT
as
set @lv_res01 = 'fn01 result 01'
set @lv_res02 = 'fn01 result 02'
select @lv_res01, @lv_res02
go
Second procedure:
create procedure sp_function02
as
declare @lv_var01 char(20)
declare @lv_res01 char(20)
declare @lv_res02 char(20)
set @lv_var01 = 'variable 01'
exec sp_function01 @lv_res01 OUTPUT, @lv_res02 OUTPUT
-- catch variables from this function
-- into lv_res01 and lv_res02
select @lv_var01, @lv_res01, @lv_res02
go
Calling sp_function01 alone (it's more or less same you just need to declare all output parameters and pass them to proc whether from inside another stored procedure or outside of it):
DECLARE @lv_res01 char(20)
DECLARE @lv_res02 char(20)
exec sp_function01 @lv_res01 OUTPUT, @lv_res02 OUTPUT
The scope of local variables is the module in which they are declared so the variables in function02 are not available in function one. You need to either use OUTPUT parameters or use the INSERT...EXEC to insert the result set of function02 into a temp table or table variable.
See how to share data between stored procedures for a thorough discussion of these and other options.
