May 21, 2013 at 12:16 pm
Hello,
I am having an issue with a dynamic pivot query(SQL 2008). I have read that using sp_executesql with parameters allows the optimizer to reuse query plans which can obviously improve performance. However I am not able to use the parameters because I keep receiving a syntax error. Here is the data I am working with:
CREATE TABLE [dbo].[tblRating]
(
[pkRating_ID] [tinyint] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[RatingName] [varchar](50) NOT NULL,
[CreatedDate] [datetime] NOT NULL DEFAULT(GetDate())
)
CREATE TABLE [dbo].[tblRating2ContentGroup]
(
[pkRating2ContentGroup_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[ContentGroupId] [int] NOT NULL,
[fkRating_ID] [tinyint] NOT NULL,
[RatingValue] [tinyint] NOT NULL,
[CreatedDate] [datetime] NOT NULL DEFAULT(GetDate()),
)
Insert Into tblRating(RatingName)
Values('Originality'),('Creativity'),('Quality')
Insert Into tblRating2ContentGroup(ContentGroupId, fkRating_ID, RatingValue)
Values
(3398, 1, 37),(3398, 2, 34),(3398, 3, 18),
(1, 1, 48) ,(1, 2, 71) ,(1, 3, 28) ,
(59, 1, 24) ,(59, 2, 38) ,(59, 3, 36) ,
(118, 1, 56) ,(118, 2, 49) ,(118, 3, 11) ,
(3169, 1, 12),(3169, 2, 18),(3169, 3, 39),
(37, 1, 64) ,(37, 2, 100) ,(37, 3, 41) ,
(3187, 1, 41),(3187, 2, 84),(3187, 3, 43),
(3188, 1, 26),(3188, 2, 34),(3188, 3, 56),
(3189, 1, 26),(3189, 2, 16),(3189, 3, 100)
And here is the query I am trying to run:
Declare
@RatingCategories nvarchar(2000),
@GroupingCategories nvarchar(2000),
@sql nvarchar(max),
@SqlParams nvarchar(500) = N'@RatingCategoriesSql nvarchar(2000), @GroupingCategoriesSql nvarchar(2000)'
Select
@RatingCategories = ISNULL(@RatingCategories + ',', '') + '[' + RatingName + ']',
@GroupingCategories = ISNULL(@GroupingCategories + ',', '') + 'MIN(' + RatingName + ') as ' + RatingName
From tblRating
SET @sql = N'With PivotTemp As
(
SELECT
ContentGroupId,
@RatingCategoriesSql
FROM
(
Select ContentGroupId,fkRating_ID,RatingValue
From tblRating2ContentGroup
)a
JOIN
(
Select pkRating_ID,RatingName
From tblRating
)b on a.fkRating_ID = b.pkRating_ID
PIVOT
(
AVG(a.RatingValue) FOR b.RatingName IN(@RatingCategoriesSql)
)pt
)
SELECT
ContentGroupId,
@GroupingCategoriesSql
FROM PivotTemp
GROUP BY ContentGroupId'
exec sp_executesql @sql, @SqlParams, @RatingCategoriesSql = @RatingCategories, @GroupingCategoriesSql = @GroupingCategories;
I keep receiving "Incorrect syntax near '@RatingCategoriesSql'". The problem is occurring on this line: AVG(a.RatingValue) FOR b.RatingName IN(@RatingCategoriesSql). If I change this query to remove the use of parameters, everything works as expected:
Declare
@RatingCategories nvarchar(2000),
@GroupingCategories nvarchar(2000),
@sql nvarchar(max)
Select
@RatingCategories = ISNULL(@RatingCategories + ',', '') + '[' + RatingName + ']',
@GroupingCategories = ISNULL(@GroupingCategories + ',', '') + 'MIN(' + RatingName + ') as ' + RatingName
From tblRating
SET @sql =
N'
With PivotTemp As
(
SELECT ContentGroupId,' + @RatingCategories + '
FROM
(
Select ContentGroupId,fkRating_ID,RatingValue
From tblRating2ContentGroup
)a
JOIN
(
Select pkRating_ID,RatingName
From tblRating
)b on a.fkRating_ID = b.pkRating_ID
PIVOT
(
AVG(a.RatingValue) FOR b.RatingName IN(' + @RatingCategories + ')
)pt
)
SELECT ContentGroupId,' + @GroupingCategories + '
FROM PivotTemp
GROUP BY ContentGroupId'
exec sp_executesql @sql
I suspect it has something to do with the brackets I am trying to use in the Pivot query, but unfortunately I am not able to see the query that sp_executesql builds. What am I doing wrong? Is it possible to do this using parameters for sp_executesql?
Thank you in advance.
Bob Pinella
May 21, 2013 at 2:02 pm
It just so happens that I tried the same thing you are yesterday. You cannot refer to a variable when using an IN query. The variable needs to called outside of the dynamic sql (so when the dynamic query actually runs, it lists the value of the variable instead of the variable intself).
Use this:
AVG(a.RatingValue) FOR b.RatingName IN('+@RatingCategories+')
Instead of this:
AVG(a.RatingValue) FOR b.RatingName IN(@RatingCategoriesSql)
Of course then you have to worry about SQL injection.
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
May 21, 2013 at 2:28 pm
Look up using the delimitedsplit8k function here. It creates a table you can join against instead of using an IN clause and will protect you from injections.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 22, 2013 at 9:06 am
Thanks for the reply Calibear. That's what I figured - I wound up using the syntax from the last query I posted which is exactly what you suggested:
AVG(a.RatingValue) FOR b.RatingName IN('+@RatingCategories+')
In this case I don't need to worry about SQL injection because @RatingCategories does not come from the client app. I am selecting it like this:
Select
@RatingCategories = ISNULL(@RatingCategories + ',', '') + '[' + RatingName + ']',
@GroupingCategories = ISNULL(@GroupingCategories + ',', '') + 'MIN(' + RatingName + ') as ' + RatingName
From tblRating
I was trying to optimize this as much as possible and have read somewhere that using the syntax from above causes the optimizer to generate a new query plan each time. I will have to do more testing when this code is moved to our production environment.
Bob Pinella
May 22, 2013 at 9:16 am
Thanks for the reply Craig. As mentioned in my previous post, I don't need to worry about SQL injection in this case.
Look up using the delimitedsplit8k function here. It creates a table you can join against instead of using an IN clause and will protect you from injections.
I will have to take a look at this function because I will most likely need to build a stored procedure that takes a comma delimited list of columns and pivot on those.
Bob Pinella
May 22, 2013 at 9:25 am
Bob Pinella (5/22/2013)
As mentioned in my previous post, I don't need to worry about SQL injection in this case.
I would argue that you should worry about it. Just because you currently are passing this data selected from a table does not protect you. You have no control over what might be in that table. Also, at some point in the future your code may be used by some other process that you have not even considered today. Since it is easy to prevent you should take the extra time to make your code robust and safe.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply