本文介绍了WHERE子句中的参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 您好我有以下存储过程,它将接受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'解决方案 在表中有但查询隐藏了表。您使用FROM(子查询)而不是FROM [table-name]。在这种情况下,子查询中的唯一列可以在where子句中访问,而不是表中的所有列... 您可以像这样更改 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 +')' )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 TotalFROM( SELECT P.DocStatus,C.CustomerName,P.CustomerID FROM dbo.CRF_Project P INNER JOIN dbo.CustomerList C ON P.CustomerID = C.CustomerID) pPIVOT( 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 hereELSE SELECT @SQL = @SQLEXEC sp_executesql @SQL, @params, @CUSTNAME, @YEARENDevery 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' 解决方案 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 thisFROM( SELECT P.DocStatus,C.CustomerName,P.CustomerID, CreateDate FROM dbo.CRF_Project P INNER JOIN dbo.CustomerList C ON P.CustomerID = C.CustomerID) pUPDATE 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+ ')') pAndIF(@CUSTNAME != '' AND @YEAR = '') SELECT @SQL = @SQL + ' WHERE CustomerName IN (' + @CUSTNAME + ')'ELSE IF(@CUSTNAME = '' AND @YEAR != '') SELECT @SQL = @SQL + ' WHERE Year([CreateDate])= (' +@YEAR+ ')' --<--error hereELSE SELECT @SQL = @SQL 这篇关于WHERE子句中的参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-20 04:40
查看更多