列名不正确createYear [英] Incorrect column name createYear

问题描述

我有以下SQL存储过程。每次执行它，它都会返回错误信息。以下是我的代码：

  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，YEAR（P.CreateDate）as createYear 
 FROM dbo.CRF_Project P 
 INNER JOIN dbo.CustomerList C 
 ON P.CustomerID = C.CustomerID 
 ）p 
 
 PIVOT 
（
 COUNT（createYear）FOR Do c状态IN（[0]，[1]，[2]，[3]，[4]，[5]，[6]，[7]）
 
）AS pvt' 
 
  SELECT   @ params  = '  @ CUSTNAME VARCHAR（MAX），
 @YEAR VARCHAR（MAX）' 
 
  IF （ @ CUSTNAME ！= ' '）
  SELECT   @ SQL  =  @ SQL  + '  WHERE CustomerName IN（' + < span class =code-sdkkeyword> @ CUSTNAME  + ' ）' 
  ELSE  
  SELECT   @ SQL  =  @SQL  
  IF （ @ YEAR ！= ' '）
  SELECT   @ SQL  =  @ SQL  + '  WHERE createYear =' + @ YEAR + ' '   -   < -error here  
  ELSE  
  SELECT   @ SQL  =  @ SQL  
 
  EXEC  sp_executesql  @ SQL ， @ params ， @ CUSTNAME ， @ YEAR  
  END  

FIY，我对@的输入CUSTNAME是'Osm'而且@YEAR是'2014'

但是如果我把它留空或只为@CUSTNAME输入，它工作正常，就在我输入@YEAR它会提示我错误

解决方案

  DECLARE   @ SQL1   NVARCHAR （MAX）
  DECLARE   @ SQL2   NVARCHAR （MAX）
  DECLARE   @ params   NVARCHAR （MAX）
  SELECT   @ SQL1  = '  SELECT P.DocStatus，C.CustomerName ，P.CustomerID，YEAR（P.CreateDate）as createYear 
 FROM d bo.CRF_Project P 
 INNER JOIN dbo.CustomerList C 
 ON P.CustomerID = C.CustomerID' 
  IF  （ @ CUSTNAME ！= ' '） 
  SELECT   @ SQL1  =  @ SQL1  + '  WHERE C.CustomerName IN（' +  @CUSTNAME  + ' ）' 
  ELSE  
  SELECT  @ SQL1 =  @ SQL1  
 < span class =code-keyword> IF （ @ YEAR ！= ' '）
  SELECT   @ SQL1  =  @ SQL1  + ' 年份（P.CreateDate）=' + @YEAR + ' ' 
  ELSE  
  SELECT   @ SQL1  =  @ SQL1  
 
 
  SELECT   @ SQL2  = '  SELECT CustomerName，[0] as Pending，[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（' +  @ SQL1  + 
 ' ）p 
 
 PIVOT 
（
 COUNT（createYear） FOR DocStatus IN（[0]，[1]，[2]，[3]，[4]，[5]，[6]，[7]）
 
）AS pvt' 
 
  SELECT   @ params  = '  @ CUSTNAME VARCHAR（MAX），
 @YEAR VARCHAR（MAX）' 
 
 
 
  EXEC  sp_executesql  @ SQL2 ， @ params ， @ CUSTNAME ， @ YEAR  
  END  

Hi, i have the following SQL stored procedure. Every time i execute it, it will return me error message. 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, YEAR(P.CreateDate) as createYear
    FROM dbo.CRF_Project P
    INNER JOIN dbo.CustomerList C
    ON P.CustomerID = C.CustomerID
) p

PIVOT
(
    COUNT (createYear) 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 + ' WHERE CustomerName IN (' + @CUSTNAME + ')'
ELSE
    SELECT @SQL = @SQL
IF(@YEAR != '')
    SELECT @SQL = @SQL + ' WHERE createYear = ' +@YEAR+ '' --<-error here
ELSE
    SELECT @SQL = @SQL

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

FIY, my input for @CUSTNAME is 'Osm' and @YEAR is '2014'
but if i leave it blank for both or input only for @CUSTNAME, it works fine, just when i input the @YEAR it will prompt me error

解决方案

DECLARE @SQL1 NVARCHAR(MAX)
DECLARE @SQL2 NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
SELECT @SQL1 ='SELECT P.DocStatus,C.CustomerName,P.CustomerID, YEAR(P.CreateDate) as createYear
    FROM dbo.CRF_Project P
    INNER JOIN dbo.CustomerList C
    ON P.CustomerID = C.CustomerID'
IF(@CUSTNAME != '')
    SELECT @SQL1 = @SQL1 + ' WHERE C.CustomerName IN (' + @CUSTNAME + ')'
ELSE
    SELECT @SQL1= @SQL1
IF(@YEAR != '')
    SELECT @SQL1 = @SQL1 + ' WHERE YEAR(P.CreateDate) = ' +@YEAR+ ''
ELSE
    SELECT @SQL1 = @SQL1


SELECT @SQL2 = '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(' + @SQL1 +
' ) p
 
PIVOT
(
    COUNT (createYear) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])
 
) AS pvt '
 
SELECT @params = '@CUSTNAME VARCHAR(MAX),
                  @YEAR VARCHAR(MAX)'
 

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

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