Must declare scalar variable in sql server dynamic sql

Question

I am having problems running sql server stored procedure. I using a dynamic sql. I get the error "Must declare the scalar variable "@EmployeeId".

SQL QUERY

ALTER PROCEDURE [dbo].[GetLeaveDays] -- Add the parameters for the stored 
procedure here
@LeaveType varchar(5000), 
@AdminId int,
@EmployeeId int

AS 
BEGIN

SET NOCOUNT ON;

DECLARE 
@queryString nvarchar(MAX);

SET @queryString = 'SELECT ' + @LeaveType + ' FROM CompulsoryLeave WHERE 
AdminId = @AdminId AND Id=(SELECT LeaveStructureId FROM Employee WHERE 
Id=@EmployeeId)';

EXECUTE sp_executesql @queryString,
    N'@AdminId int',
    N'@EmployeeId int',
     @AdminId = @AdminId,
     @EmployeeId=@EmployeeId

END

Bobby Tables can't wait to get a hold of @LeaveType. You might want to validate it against the columns in the table unless you need something more interesting, e.g. expressions. If it is a list of columns then you can split it and validate them individually. — HABO
– HABO, Commented Jan 21, 2018 at 4:20

Dan Guzman · Accepted Answer · 2018-01-20 21:36:30Z

2

The second sp_executesql parameter should be a single string with all parameter definitions. Try:

EXECUTE sp_executesql @queryString,
    N'@AdminId int, @EmployeeId int',
     @AdminId = @AdminId,
     @EmployeeId = @EmployeeId;

answered Jan 20, 2018 at 21:36

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ilyes Over a year ago

While you respect the order of the parameters, you can just pass the value directly without set it to the parameters as ... @AdminId, @EmployeeId;

Dan Guzman Over a year ago

@Sami, it is true one can specify parameters by ordinal position rather than name with sp_executesql (and any stored procedure) but IMHO the named syntax is more maintainable, especially when the outer reference is named differently.

Collectives™ on Stack Overflow

Must declare scalar variable in sql server dynamic sql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related