字符串'2010-20'后面的未闭合引号。 “2010-20”附近的语法不正确 [英] Unclosed quotation mark after the character string '2010-20'. Incorrect syntax near '2010-20'

问题描述

这是带有数据透视表的存储过程，用于生成这样的数据



జల2007 2007 2007-2008 2008-2009 2009-2010 2010-2011 2011-2012
Chittoor 14000 12000 15500 2376 3425

总计14000 12000 15500 55236 436346



执行时我收到此错误

字符串'2010-20'后的未闭合引号。 2010-20附近的语法不正确

This is stored procedure with pivot table to generate data like this

జిల్లా 2007-2008 2008-2009 2009-2010 2010-2011 2011-2012
Chittoor 14000 12000 15500 2376 3425
Total 14000 12000 15500 55236 436346

while executing i am getting this error
Unclosed quotation mark after the character string '2010-20'. Incorrect syntax near '2010-20'

CREATE procedure year_wise_close_bal
as
begin
DECLARE @fin_year NVARCHAR (500)
SELECT @fin_year = COALESCE (@fin_year  + ',[' + fin_year + ']', '[' + fin_year + ']')
FROM    after_audit_cc_trans
declare @sql NVARCHAR(500)
SET @sql='select * from(select insure10.dbo.Districtmst.distname as dname,
after_audit_cc_trans.fin_year as year,after_audit_cc_trans.close_bal as close_bal
from after_audit_cc_trans inner join insure10.dbo.Districtmst ON after_audit_cc_trans.distcode = insure10.dbo.Districtmst.distcode)as t1
pivot (sum(close_bal) for year in ('+@fin_year+')) as bal'
PRINT @sql
execute(@sql)
End

推荐答案

根据RyanDev的说法建议我发布此解决方案...

BulletVictim [ ^ ]说

As per RyanDev's suggestion I'm posting this solution ...
BulletVictim[^] said

引用：

试试两个'围绕@fin_year

（''+ @ fin_year +''））

try two ' around the @fin_year
(''+@fin_year+''))

我回复了

I responded

Quote：

你的错误在@sql中。你可以发布PRINT @sql命令的输出。 BulletVictim是正确的 - 你似乎缺少字符串值周围的单引号。

Your error is in @sql. Can you post the output from the PRINT @sql command. BulletVictim is correct - you appear to missing single quotes around the string value.

OP随后声称通过添加组解决了问题

OP has subsequently claimed that adding group by solved the problem

SELECT @fin_year = COALESCE (@fin_year  + ',[' + fin_year + ']', '[' + fin_year + ']')
FROM    after_audit_cc_trans group by fin_year

这个工作的原因可以通过检查内容来确定 @fin_year 因为这是bei ng插入 @sql 字符串，用于 IN 子句。

The reason this works could be determined by examining the contents of @fin_year as this is being inserted into the @sql string for use in an IN clause.

in ('+@fin_year+'))

所以预期的格式为IN（'value1'，'value2'，'value3'）等。



这里的技巧是（正如OP所做的那样）打印SQL，提供了执行命令。当您可以看到错误报告的字符时，通常会更容易看到问题...在此实例中，2010-20'附近的语法不正确。

so the expected format is IN('value1','value2', 'value3') etc.

The "trick" here is (as the OP has done) to print the SQL that the execute command has been provided. It is usually far easier to see the problem when you can see the characters that the error reports ... "Incorrect syntax near '2010-20'" in this instance.

这篇关于字符串'2010-20'后面的未闭合引号。 “2010-20”附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！