CODEWITHSUNDEEP
sqlsql-servert-sql
Izuagbala
Izuagbala

Reputation: 449

Must declare scalar variable in sql server dynamic sql

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

Upvotes: 1

Views: 3595

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

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;

Upvotes: 2

Related Questions