WHERE子句中的参数 [英] Parameters in WHERE clause

问题描述

您好我有以下存储过程，它将接受2个参数。第一个是@CUSTNAME，第二个是@YEAR。 @CUSTNAME工作正常，但@YEAR有错误。以下是我的代码：

  DECLARE   @ SQL   NVARCHAR （MAX）
  DECLARE   @ params   NVARCHAR （MAX）
 
  SELECT   @ SQL  = '  SELECT CustomerName，[ 0]为待定，[1]为Save_As_Draft，[2]为Spec_Confirmed，[3]为Request_Revision，[4]为Withdraw，[5]为Pending_RND，[6]为Pending_QC，[7]为Manday_Confirmed，[0] + [1] + [2] + [3] + [4] + [5] + [6] + [7]为总计
 FROM 
（
 SELECT P.DocStatus，C .CustomerName，P.CustomerID 
 FROM dbo.CRF_Project P 
 INNER JOIN dbo.CustomerList C 
 ON P.CustomerID = C.CustomerID 
）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 ！= ' '  AND   @ YEAR  = ' '）
  SELECT   @ SQL  =  @ SQL  + '  WHERE CustomerName IN（' +  @CUSTNAME  + ' ）' 
  ELSE   IF （ @ CUSTNAME  = ' '  AND   @ YEAR ！= ' '）
  SELECT   @ SQL  =  @ SQL  + '  WHERE年（[CreateDate]）=（' + @ YEAR + ' ）'   -   <  - 此处出错 
  ELSE  
  SELECT   @ SQL  =  @ SQL  
 
  EXEC  sp_executesql  @ SQL ，  @ params ， @ CUSTNAME ， @ YEAR  
  END  

每次执行存储过程时，它都会给我以下错误消息：

错误的列名'CreateDate'。



我的输入是@CUSTNAME'Osm'和@YEAR '2014'

解决方案

Quote：

但我的表中有CreateDate

在表中有但查询隐藏了表。您使用FROM（子查询）而不是FROM [table-name]。在这种情况下，子查询中的唯一列可以在where子句中访问，而不是表中的所有列...

您可以像这样更改

< pre lang =SQL> FROM
（
SELECT P.DocStatus， C.CustomerName，P.CustomerID， CreateDate
FROM dbo.CRF_Project P
INNER JOIN dbo.CustomerList C
ON P.CustomerID = C.CustomerID
）p

UPDATE根据OP评论......

  FROM  
（
  SELECT  P.DocStatus，C.CustomerName，P.CustomerID 
  FROM  dbo.CRF_Project P 
  INNER  加入 dbo.CustomerList C 
  ON  P.CustomerID = C.CustomerID 
   WHERE 年份（[CreateDate]）=（ '  + @ YEAR +'）' < span class =code-string>  
）p  

和

  IF （ @ CUSTNAME ！= '  '  AND   @ YEAR  = ' '）
  SELECT   @ SQL  =  @ SQL  + '  WHERE CustomerName IN（' +  @ CUSTNAME  + ' ）' 
   ELSE   IF （ @ CUSTNAME  = ' '  AND   @ YEAR ！= ' '）
  SELECT   @ SQL  =  @ SQL  + '  WHERE Year（[CreateDate]）=（' + @YEAR + ' ）'   -   < ;  - 错误在这里  
  ELSE  
  SELECT   @ SQL  =  @ SQL  

Hi i have the following stored procedure which will accept 2 params. The first is @CUSTNAME and the second is @YEAR. @CUSTNAME works fine but @YEAR have an error. Below are my codes:

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
) 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 != '' AND @YEAR = '')
    SELECT @SQL = @SQL + ' WHERE CustomerName IN (' + @CUSTNAME + ')'
ELSE IF(@CUSTNAME = '' AND @YEAR != '')
    SELECT @SQL = @SQL + ' WHERE Year([CreateDate])= (' +@YEAR+ ')' --<--error here
ELSE
    SELECT @SQL = @SQL

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

every time i execute the stored procedure ,it will give me the following error message:
Incorrect column name 'CreateDate'.

My input is for @CUSTNAME 'Osm' and @YEAR '2014'

解决方案

Quote:

but there is CreateDate in my table

In the table there is but the query hides the table. You use FROM (sub-query) from instead of FROM [table-name]. In this case the only columns in the sub-query accessible in the where clause and not all the columns in the table...
You may change it like this

FROM
(
    SELECT P.DocStatus,C.CustomerName,P.CustomerID, CreateDate
    FROM dbo.CRF_Project P
    INNER JOIN dbo.CustomerList C
    ON P.CustomerID = C.CustomerID
) p

UPDATE according OPs comments...

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

And

IF(@CUSTNAME != '' AND @YEAR = '')
    SELECT @SQL = @SQL + ' WHERE CustomerName IN (' + @CUSTNAME + ')'
ELSE IF(@CUSTNAME = '' AND @YEAR != '')
    SELECT @SQL = @SQL + ' WHERE Year([CreateDate])= (' +@YEAR+ ')' --<--error here
ELSE
    SELECT @SQL = @SQL

这篇关于WHERE子句中的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！