Must declare the scalar variable (Stored procedure)

1
votes

I saw this error in some pages, but in my case, I cannot find the problem here.

The error comes when execute my procedure.

exec sp_executesql N' EXEC UpdateEmployee @EmployeeID, @Status ',N'@EmployeeID bigint,@Status int',@EmployeeID=2,@Status=5

CREATE PROCEDURE UpdateEmployee
(
    @EmployeeID BIGINT,
    @StatusID int
)
AS

IF @StatusID = 0
BEGIN
    SET @StatusID = null
END 

UPDATE Employee SET StatusID = @StatusID WHERE EmployeeID = @EmployeeID
GO

I got the following error:

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

My field StatusID on the table employee is of type int and nullable

I didn't forget to declare the variable. In fact, it is an IN parameter of my procedure. So, what's wrong?

UPDATE - FIXED

I realized the problem comes from the query generated by the library. I was using the parameter @Status instead of @StatusID. That was the problem. Thanks for your help.

3
sql-serverstored-proceduresscalar
Why you want to execute your sp using dynamic sql ? you can execute it like this - exec UpdateEmployee @EmployeeID=2,@StatusID=5 – Krishnraj Rana
It's the output generated by an ORM library – Maximus Decimus
Right click on procedure from SSMS and select execute. Pass the parameters and see what happens. – shadow

3 Answers

1
votes

You are missing the required BEGIN ... END around a multi-statement SP body. This code works fine:

create table Employee (
    StatusID int,
    EmployeeID  int
)
go

CREATE PROCEDURE UpdateEmployee
(
    @EmployeeID BIGINT,
    @StatusID int
)
AS
begin
    IF @StatusID = 0
    BEGIN
        SET @StatusID = null
    END 

    UPDATE Employee SET StatusID = @StatusID WHERE EmployeeID = @EmployeeID
end
GO

exec UpdateEmployee 0,0
go
1
votes

There is no problem with your proc its how you are executing it. Since you are executing it dynamically you must declare the param you are passing in your dynamic query .

Execute like this

exec sp_executesql N' EXEC UpdateEmployee 2, 5 '
0
votes

try this:

CREATE PROCEDURE UpdateEmployee
(
    @EmployeeID BIGINT,
    @StatusID int
)
AS
UPDATE Employee SET StatusID = NullIf( @StatusID, 0 ) WHERE EmployeeID = @EmployeeID
GO