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'.
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