Incorrect Syntax Running Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Incorrect Syntax Running Query

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-06-12 : 11:10:20

I'm getting:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '('.

If highlight the first 8 rows and run the query it completes successfully. If I highlight the remaining it completes. When I run the entire thing together I get the message.
Running SQL 2008


DECLARE @QUERY VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
                        '],[' + (str(year((oehdrhst_sql.inv_dt))))
                        FROM oehdrhst_sql
                        ORDER BY '],[' + (str(YEAR(oehdrhst_sql.inv_dt)))
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

 

SET @query =
'SELECT * FROM
(
SELECT OEHDRHST_SQL.cus_no,YEAR(OEHDRHST_SQL.inv_dt),OEHDRHST_SQL.tot_sls_amt

    FROM oehdrhst_sql
    )t

PIVOT (SUM(OEHDRHST_SQL.tot_sls_amt) FOR year(oehdrhst_sql.inv_dt)
IN ('+@years+'))) AS pvt'

 

EXECUTE (@query)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-12 : 11:41:20

Change your "execute (@query)"
to: print @query

the problem should then be more obvious. Once you resolve the syntax error then switch back to execute.

Be One with the Optimizer
TG

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-12 : 12:20:59

Aren't there too many cloase brackets after @years+ '

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources