Dynamic Pivot Using sp_executeSQL with Parameters

  • 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

  • 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

  • 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.


    - Craig Farrell

    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

  • 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

  • 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

  • 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