CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Nadir
Nadir

Reputation: 40

Return multiple output from a stored procedure

It it possible to return multiple output values to a parent stored procedure using

exec 'stored procedure name' 'inputval','outval1','outval2'

exec sp_SubSalaryCalcuation @sec1_in,@sec1_out,@worktimefrm,@worktimeto,@out_timestatus OUTPUT,@out_status OUTPUT,@out_overtime OUTPUT

I need to call this stored procedure 

ALTER PROCEDURE [dbo].[sp_SubSalaryCalcuation] 
@out_overtime nvarchar(100) OUTPUT,
@out_status nvarchar(100) OUTPUT,
@out_overtimestatus nvarchar(100) OUTPUT,
@sec1_in nvarchar(200),
@sec1_out nvarchar(200),
AS
BEGIN
SET @out_status=@sec1_in
SET @out_overtime=0
SET @out_overtimestatus=0

END

Upvotes: 1

Views: 13388

Answers (1)

Sarath Subramanian
Sarath Subramanian

Reputation: 21271

Yes. You can do that. You can get multiple output parameters from another procedure to parent procedure. Consider the following example.

Note : You should pass the parameters in the same order that is present in sp_SubSalaryCalcuation procedure.

1. Parent Procedure

CREATE PROCEDURE ParentProcedure

    @Param1 nvarchar(30)

AS
BEGIN

    DECLARE @out_timestatus nvarchar(100)
    DECLARE @out_status nvarchar(100)
    DECLARE @out_overtime nvarchar(100)

    SET NOCOUNT ON;

    -- Send parameters in the same order of sp_SubSalaryCalcuation
    exec sp_SubSalaryCalcuation 1,2,3,4,@out_timestatus OUTPUT,@out_status OUTPUT,@out_overtime OUTPUT

    PRINT @out_timestatus
    PRINT @out_status
    PRINT @out_overtime

END
GO

2. Child procedure

CREATE PROCEDURE sp_SubSalaryCalcuation 
@sec1_in nvarchar(200),
@sec1_out nvarchar(200),
@worktimefrm nvarchar(200),
@worktimeto nvarchar(200),
@out_overtime nvarchar(100) OUTPUT,
@out_status nvarchar(100) OUTPUT,
@out_overtimestatus nvarchar(100) OUTPUT

AS
BEGIN

    SET NOCOUNT ON;

    SET @out_overtime = 10
    SET @out_status = 20
    SET @out_overtimestatus = 30

END
GO

Result in you parameter of parent procedure will be as follows

x-----------------x----------x
|     Variable    |   Value  |
x-----------------x----------x
| @out_timestatus |   10     |
| @out_status     |   20     |
| @out_overtime   |   30     |
x-----------------x----------x

Upvotes: 2

Related Questions