AND附近的语法不正确 [英] Incorrect syntax near AND

问题描述

当我执行程序时，我有以下程序。它总是给我错误AND附近的语法不正确。我不知道它有什么问题。我经历了很多次代码，无法找到错误。以下是我的程序：

Hi i have the following procedure, when i execute the procedure. it always give me the error "Incorrect syntax near AND". I dont have idea on what is wrong with it. I have gone through many times the codes and cant find what is the error. Below are my procedure:

<pre lang="sql">DECLARE @SQL NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)

SELECT @SQL = 'SELECT CustomerName,[0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed, [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total
FROM
(
    SELECT P.DocStatus,C.CustomerName,P.CustomerID
    FROM dbo.CRF_Project P
    INNER JOIN dbo.CustomerList C
    ON P.CustomerID = C.CustomerID
    WHERE Year([CreateDate])= '+@YEAR+'
) p

PIVOT
(
    COUNT (CustomerID) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])

) AS pvt '

SELECT @params = '@CUSTNAME VARCHAR(MAX),
                  @YEAR VARCHAR(MAX)'

IF(@CUSTNAME != '')
    SELECT @SQL = @SQL + ' AND CustomerName IN (' + @CUSTNAME + ')'
ELSE
    SELECT @SQL = @SQL

EXEC sp_executesql @SQL, @params, @CUSTNAME, @YEAR
END

推荐答案

只需更改

' AND CustomerName IN (' + @CUSTNAME + ')'

into

' WHERE CustomerName IN (' + @CUSTNAME + ')'

< b r />
，如

as in the

SELECT @SQL = @SQL + ' AND CustomerName IN (' + @CUSTNAME + ')'

SQL Server将@SQL与后面的新AND语句结合起来。因此，AND语句不会附加在枢轴内的WHERE后面，而是在枢轴p之后。不应使用AND，而应使用WHERE。

the SQL Server will combine the @SQL with the new AND statement behind. Therefore the AND statement is not append behind the WHERE inside the pivot but after the pivot p. Instead of using AND, WHERE should be used.

这篇关于AND附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！