Solved

Dynamic SQL Error - Incorrect syntax near ')'.

Posted on 2011-09-05
4
867 Views
Last Modified: 2012-05-12
Hi All,

Can anyone suggest why I am receiving the following error within the attached SQL. The error is on the line 4 'SET @query = '..

Error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

Thanks,

Rit
DECLARE @cols NVARCHAR(2000), @query NVARCHAR(4000)

	SELECT @cols = COALESCE(@cols + ',' + QUOTENAME(Car_Name), QUOTENAME(Car_Name)) FROM lookup_cars ORDER BY Car_Name, OrderSeq

	SET @query = 
	'SELECT *  FROM 
	(
		view_new_cars
	)t

	PIVOT (COUNT(t.CarID) FOR T.Car_Name IN (' + @cols + ')) as PVT'

	EXEC(@query)

Open in new window

0
Comment
Question by:rito1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
x-men earned 500 total points
ID: 36484478
use a "print @query" to check it the string is ok
0
 
LVL 1

Author Closing Comment

by:rito1
ID: 36484496
Good thinking! :-)
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 36484823
And the error is? You should post that usually, as the accepted post might have helped, but isn't the solution. In such cases please post the solution, accept it and optionally award assist points to experts' posts.

The error is: You need to cut off the first comma from @cols, and make sure there is always a value, even if the select to construct @cols does not return any rows.
Instead, I would insert a dummy value into the final SQL:
SET @query = 
        'SELECT *  FROM 
        (
                view_new_cars
        )t

        PIVOT (COUNT(t.CarID) FOR T.Car_Name IN ( '''' ' + @cols + ')) as PVT'

Open in new window

0
 
LVL 1

Author Comment

by:rito1
ID: 36487029
Thanks for the advice Qlemo.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
(sql serv16)ssis 2016 question/check 1 107
Using this function 4 42
How to add a suffix to a value in a column based on the value in another column 4 32
SQL- GROUP BY 4 25
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question