Unclosed quotation mark after the character string '2010-20'. Incorrect syntax near '2010-20'

Question

0.00/5 (No votes)

See more:

, +

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'

SQL

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

Posted 5-Mar-14 0:47am

Sateesh Pasupulla

Add a Solution

Comments

BulletVictim 5-Mar-14 8:35am

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

CHill60 5-Mar-14 8:38am

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.

ZurdoDev 5-Mar-14 10:17am

I suggest you post as solution.

Sateesh Pasupulla 5-Mar-14 11:46am

Thank you friends for replay.
I got Solution for this query
when i was added ---> group by fin_year
to the
SELECT @fin_year = COALESCE (@fin_year + ',[' + fin_year + ']', '[' + fin_year + ']')
FROM after_audit_cc_trans group by fin_year

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2014-03-07T06:54:00

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

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

I responded

Quote:
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 has subsequently claimed that adding group by solved the problem

SQL

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

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+'))

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.