How to call a stored procedure that has two OUTPUT parameters from another stored procedure

Question

Apologies if this has been asked before, but I wasn't able to find anything that worked for me.

I have a stored procedure that has two OUTPUT parameters:

CREATE PROCEDURE dbo.xxx
    .
    .
    .
    @param1 INT OUTPUT,
    @param2 INT OUTPUT 
AS

and I want to call it from another stored procedure.

I know how to do it when there is one OUTPUT parameter, but I don't know how to call it so I also get the value of the second one. Any ideas?

Thanks in advance :)

https://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure#comment1344628_1492446 this is a good start. — GeorgiG, Feb 28 '18 at 12:18

score 5 · Accepted Answer · answered Feb 28 '18 at 12:19

Here is one way to do it:

Sample procedure with two output parameters

CREATE PROCEDURE SumAndMultiply
(
    @In int,
    @OutSum int output,
    @OutMultiply int output
)
AS

    SELECT  @OutSum = @In + @In,
            @OutMultiply = @In * @In

GO

Sample procedure that executes the first one:

CREATE PROCEDURE executeSumAndMultiply
(
    @In int
)
AS

DECLARE @Out1 int, 
        @Out2 int

EXEC SumAndMultiply @In = @In, @OutSum = @Out1 OUTPUT, @OutMultiply = @Out2 OUTPUT

SELECT @Out1 As Out1, @Out2 As Out2

GO

Execute the second procedure:

EXEC executeSumAndMultiply 3

Results:

Out1    Out2
6        9

See a live demo on rextester

That did it, thank you very much for the fast response! (I have to wait 4 more minutes to accept your post as the answer) — Anna P., Feb 28 '18 at 12:24

score 1 · Answer 2 · answered Feb 28 '18 at 12:22

create procedure first_proc(@p1 int, @p2 int out, @p3 int out)
as
    set @p2 = 1;
    set @p3 = 10;
GO

✓

create procedure second_proc
as
    declare @f1 int;
    declare @f2 int;

    exec dbo.first_proc 10, @f1 out, @f2 out;

    select 'Returned values:' t, @f1, @f2;
GO

✓

exec dbo.second_proc;
GO

t                | (No column name) | (No column name)
:--------------- | ---------------: | ---------------:
Returned values: |                1 |               10

dbfiddle here

score 0 · Answer 3 · answered Feb 28 '18 at 12:19

Try to follow the below Approach, i Just given a sample example

CREATE PROCEDURE usp_NestedSP
    @CurrentDate DATETIME OUT
AS
BEGIN
    SET @CurrentDate = GETDATE()
END
GO
--Nested SP which accepts OUTPUT parameter
CREATE PROCEDURE usp_MainSP
AS
BEGIN
    DECLARE @CurrentDate DATETIME
    EXEC [usp_NestedSP] @CurrentDate OUTPUT
    SELECT @CurrentDate AS 'ResultFromNestedSP'

END
GO

EXEc usp_MainSP

How to call a stored procedure that has two OUTPUT parameters from another stored procedure

3 Answers3

Linked