使用条件where子句时,Sql存储过程返回语法错误 [英] Sql stored procedure returning syntax error when using conditional where clause
问题描述
我有以下存储过程,它有2个参数。 @Class参数可以包含11个值中的任何一个。取决于where子句查看不同列的值。
该过程编译正常,如果我复制打印并将其粘贴到新的查询窗口,它运行正常,但是,如果我执行该过程,我在'Bristol'附近得到一个不正确的语法错误消息,但无法弄清楚我需要做什么来修复,尽管在这里和其他网页上查看。任何帮助将不胜感激
我尝试过:
I have the following stored procedure which takes 2 parameters. The @Class parameter can have any one of 11 values. Depending on the value the where clause looks at a different column.
The procedure compiles ok and if I copy the print and paste it into a new query window it runs fine, but if I execute the procedure I am getting an Incorrect syntax error message near 'Bristol' but cannot figure out what I need to do to fix despite looking on here and on other web pages. Any help would be much appreciated
What I have tried:
ALTER PROCEDURE [TTR_HazDriver]
@Depot nvarchar(50),
@Class nvarchar(1)
AS
BEGIN
DECLARE @Where nvarchar(1000)
DECLARE @sSql nvarchar(MAX)
DECLARE @Order nvarchar(1000)
SET @sSql = '
SELECT EM.EmployeeNumber
, EM.EmployeeSurname
, EM.EmployeeInitials
, D.Depot
, EDL.Class1
, EDL.Class2
, EDL.Class3
, EDL.Class4
, EDL.Class5
, EDL.Class6
, EDL.Class7
, EDL.Class8
, EDL.Class9
, CONVERT(VARCHAR(10),EDL.ExpiryDate, 103) ExpiryDate
, EDL.Tanks
, EDL.Package
FROM EmployeeMaster EM
LEFT OUTER JOIN PayrollFrequency PF ON EM.FrequencyDesc = PF.DescCode
INNER JOIN EmployeeDrivingLicence EDL ON EM.EmpCode = EDL.EmpCode
LEFT OUTER JOIN Depot D ON EM.Depot = D.DescCode'
SET @Where = '
WHERE (D.Depot = ''' + @Depot + ''' OR ''' + @Depot + ''' IS NULL)
AND EM.EmployeeLeft = ''N''
AND PF.FrequencyDesc = ''Weekly'''
SET @Order = '
ORDER BY D.DepotDepotDescription
, EDL.ExpiryDate'
IF @Class = '1'
SET @Where = @Where + ' AND EDL.Class1 = ''Y'''
IF @Class = '2'
SET @Where = @Where + ' AND EDL.Class2 = ''Y'''
IF @Class = '3'
SET @Where = @Where + ' AND EDL.Class3 = ''Y'''
IF @Class = '4'
SET @Where = @Where + ' AND EDL.Class4 = ''Y'''
IF @Class = '5'
SET @Where = @Where + ' AND EDL.Class5 = ''Y'''
IF @Class = '6'
SET @Where = @Where + ' AND EDL.Class6 = ''Y'''
IF @Class = '7'
SET @Where = @Where + ' AND EDL.Class7 = ''Y'''
IF @Class = '8'
SET @Where = @Where + ' AND EDL.Class8 = ''Y'''
IF @Class = '9'
SET @Where = @Where + ' AND EDL.Class9 = ''Y'''
IF @Class = 'T'
SET @Where = @Where + ' AND EDL.Tanks = ''Y'''
IF @Class = 'P'
SET @Where = @Where + ' AND EDL.Package = ''Y'''
SET @sSql = @sSql + @Where + @Order
PRINT @sSql
EXEC sp_executesql @sSQL, @Depot, @Class
END
The print statement produces the following query:
SELECT EM.EmployeeNumber
, EM.EmployeeSurname
, EM.EmployeeInitials
, D.Depot
, EDL.Class1
, EDL.Class2
, EDL.Class3
, EDL.Class4
, EDL.Class5
, EDL.Class6
, EDL.Class7
, EDL.Class8
, EDL.Class9
, CONVERT(VARCHAR(10),EDL.ExpiryDate, 103) ExpiryDate
, EDL.Tanks
, EDL.Package
FROM EmployeeMaster EM
LEFT OUTER JOIN PayrollFrequency PF ON EM.FrequencyDesc = PF.DescCode
INNER JOIN EmployeeDrivingLicence EDL ON EM.EmpCode = EDL.EmpCode
LEFT OUTER JOIN Depot D ON EM.DepotDepotDescription = D.DescCode
WHERE (D.DepotDepotDescription = 'Bristol' OR 'Bristol' IS NULL)
AND PF.FrequencyDesc = 'Weekly'
AND EDL.Class3 = 'Y'
ORDER BY D.Depot
, EDL.ExpiryDate
推荐答案
改变了这一行
Changed this line from
EXEC sp_executesql @sSQL, @Depot, @Class
To:
To:
EXEC sp_executesql @sSQL
这篇关于使用条件where子句时,Sql存储过程返回语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!