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 :)
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
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; GOt | (No column name) | (No column name) :--------------- | ---------------: | ---------------: Returned values: | 1 | 10
dbfiddle here
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
