Msg 137, Level 15, State 2, Server ip-172-31-36-11, Line 2 Must declare the scalar variable "@Deptno"

0
votes

My stored procedure (below) gives the following error:

Msg 137, Level 15, State 2, Server ip-172-31-36-11, Line 2 Must declare the scalar variable "@Deptno"

CREATE PROCEDURE EmployeesDept 
    @Deptno char(3) 
AS 
    SELECT lastname AS Name 
    FROM Employee 
    WHERE workdept = @Deptno
GO

EXECUTE EmployeesDept @Deptno
GO
1
sqlsql-servertsqlstored-procedures
Please ensure you write complete questions, which contain all the information and an actual question. – Dale K

1 Answers

1
votes

When it comes to executing a stored procedure you have to give the parameters values if you want it to work, and you can execute positionally or by name. You can put values in directly, or you can store values in variables and use those variables to give values to the stores procedure

--direct values 
execute EmployeesDept 'abc', 0 --positional 
execute EmployeesDept @Deptno='abc', @whatever=0 --named parameters 

--values from variables
DECLARE @n CHAR(3) = 'abc';
DECLARE @i INT = 0;

execute EmployeesDept @n, @i --position based
execute EmployeesDept @Deptno = @n, @whatever = @i --name based

Named based parameters do not have to be in order, positional ones do

A better habit (for reasons of taking your skills to another DB) for CREATE PROCEDURE is like:

CREATE PROCEDURE EmployeesDept (
  @Deptno char(3)
) AS

with parentheses around the argument list. For 2 or more args, separate with a comma. Example:

CREATE PROCEDURE EmployeesDept (
  @Deptno char(3),
  @whatever INT
) AS

Also get into the habit of ending each statement in a stored procedure with a semicolon