• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • Last Modified:

Dynamic SQL Error - Incorrect syntax near ')'.

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
rito1
Asked:
rito1
  • 2
1 Solution
 
x-menIT super heroCommented:
use a "print @query" to check it the string is ok
0
 
rito1Author Commented:
Good thinking! :-)
0
 
QlemoC++ DeveloperCommented:
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
 
rito1Author Commented:
Thanks for the advice Qlemo.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now