使用条件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屋！