必须在sql server动态sql中声明标量变量 [英] Must declare scalar variable in sql server dynamic sql

问题描述

我在运行 sql server 存储过程时遇到问题.我使用动态sql.我收到错误必须声明标量变量@EmployeeId".

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 查询

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

推荐答案

第二个 sp_executesql 参数应该是包含所有参数定义的单个字符串.试试:

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;

这篇关于必须在sql server动态sql中声明标量变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！