Search code examples
sqlsql-serverstored-proceduresoutput-parameter

How to make an output parameter in Stored Procedure in my scenario?


I have a query, that will insert a data into my table, and it should return one value

My Stored Procedure

ALTER PROC FspCreateRequest 
@RequestDescription nvarchar(200),
@CreatedBy varchar(30),
@CreatedDate datetime,
@Region nchar(10),
@RequestID varchar(20) output
AS
BEGIN
    DECLARE @maxRequestID AS varchar(20)
    SELECT @maxRequestID=max(RequestID) 
    FROM [F_CheckRequest]
    WHERE Region = @Region

    IF (@maxRequestID IS Null or @maxRequestID = '')
    BEGIN
        
        IF(@Region = 'Jeddah')
        BEGIN
        
            INSERT INTO [F_CheckRequest
                (RequestID,RequestDescription,CreateBy,CreatedDate,Region) 
            VALUES 
               ('10001',@RequestDescription,@CreatedBy,@CreatedDate,@Region)

            SELECT @RequestID = '10001'
        END
        ELSE
        BEGIN
            INSERT INTO [F_CheckRequest] 
                (RequestID,RequestDescription,CreateBy,CreatedDate,Region) 
            VALUES 
               ('50001',@RequestDescription,@CreatedBy,@CreatedDate,@Region)

            SELECT @RequestID = '50001'
        END
    END
    ELSE
    BEGIN
        SET @maxRequestID=@maxRequestID+1;
        INSERT INTO [F_CheckRequest] 
             (RequestID,RequestDescription,CreateBy,CreatedDate,Region) 
        VALUES 
         (@maxRequestID,@RequestDescription,@CreatedBy,@CreatedDate,@Region)

        SELECT @RequestID = @maxRequestID
    END
END

When I run this query by following code

EXECUTE FspCreateRequest @RequestDescription='descriotion',@CreatedBy='sa',@CreatedDate='2017-01-10',@Region='Mecca',@ReqID out
PRINT 'RequestID'+ @ReqID 

It throws error

Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@ReqID".

Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@ReqID".

Updated

Yes I did mistake. I missed to declare the output parameter. But after I execute like below

DECLARE @ReqID varchar(30)
EXECUTE FspCreateRequest @RequestDescription='descriotion',@CreatedBy='sa',@CreatedDate='2017-01-10',@Region='Jeddah',@ReqID out
PRINT 'RequestID'+ @ReqID 

Still throws error like below

Msg 119, Level 15, State 1, Line 3

Must pass parameter number 5 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.


Solution

  • This is the correct answer

    DECLARE @ReqID varchar(30);
    EXEC FspCreateRequest @RequestDescription='descriotion',@CreatedBy='sa',@CreatedDate='2017-01-10',@Region='Mecca',@RequestID = @ReqID out;
    PRINT 'RequestID'+ @ReqID ;
    

    Thank you @DanGuzman