SQL Server: how to insert variables in dynamic Select

Question

I have a stored procedure that I want to make dynamic so that it can handle varying inputs. I got most of the dynamic procedure figured out except for the following.

Can someone tell me how I have to write this part in order to use it in a dynamic procedure ? Do I just have to put '+ before and +' after each variable ?

INSERT INTO @temp
(
            dateRange
)
SELECT      @date0
UNION ALL
SELECT      @date1
UNION ALL
SELECT      @date2
UNION ALL
SELECT      @date3
UNION ALL
SELECT      @date4
UNION ALL
SELECT      @date5

Edit: The whole query as FYI. Before I made this dynamic it was working correct so my guess is I have some missing or unneeded quotes here or any other writing mistake. Also, I am not sure if the Where conditions in the last nested query are written correct to be dynamic.

ALTER PROCEDURE [dbo].[FetchHistoryCombined]
    @selection nvarchar(100),
    @date0 nvarchar(20),
    @date1 nvarchar(20),
    @date2 nvarchar(20),
    @date3 nvarchar(20),
    @date4 nvarchar(20),
    @date5 nvarchar(20)
AS
BEGIN   
    SET NOCOUNT ON;

    BEGIN

    DECLARE @sql nvarchar(max)

    SET @sql = N' DECLARE @temp AS TABLE
    (
                dateRange nvarchar(20)
    )
    DECLARE @temp2 AS TABLE
    (
                ranking int,
                item nvarchar(100),
                volume int
    )

    INSERT INTO @temp
    (
                dateRange
    )
    SELECT      ' + @date0 + '
    UNION ALL
    SELECT      ' + @date1 + '
    UNION ALL
    SELECT      ' + @date2 + '
    UNION ALL
    SELECT      ' + @date3 + '
    UNION ALL
    SELECT      ' + @date4 + '
    UNION ALL
    SELECT      ' + @date5 + '

    INSERT INTO @temp2
    (       
                ranking,
                item,
                volume
    )
    SELECT      Top 10 RANK() OVER(ORDER BY COUNT(*) desc, ' + @selection + ') [Rank],
                ' + @selection + ', 
                COUNT(*) AS volume
    FROM        LogEsc 
    WHERE       dateEsc LIKE ''' + @date0 + '%''
    AND         EID LIKE ''PE%''
    GROUP BY    ' + @selection + '
    ORDER BY    volume desc, ' + @selection + '

    SELECT      
                (
                        SELECT      A.item
                        FROM        @temp2 A
                        ORDER BY    A.ranking, A.item
                        FOR XML PATH(''''), ELEMENTS, TYPE
                ) AS top10,
                (       
                        SELECT      B.dateRange,
                                    (
                                            SELECT      C.item,
                                                        (
                                                                SELECT      COUNT(*) AS volume
                                                                FROM        LogEsc D
                                                                WHERE       D.' + @selection + ' = C.item
                                                                AND         D.EID LIKE ''PE%''
                                                                AND         D.dateEsc LIKE B.dateRange + ''%''
                                                                FOR XML PATH(''''), ELEMENTS, TYPE
                                                        )
                                            FROM        @temp2 C
                                            ORDER BY    C.ranking, C.item
                                            FOR XML PATH(''''), ELEMENTS, TYPE
                                    ) AS [dateRange/items]
                        FROM    @temp B
                        FOR XML PATH(''''), ELEMENTS, TYPE
                ) AS history
    FOR XML PATH(''ranking''), ELEMENTS, TYPE, ROOT(''ranks'')'

    EXEC(@sql)

    END
END

Many thanks in advance, Mike.

Dynamic SQL refers to putting queries into strings and then executing them. Your query isn't in a string. — Gordon Linoff
– Gordon Linoff, Commented Jun 8, 2014 at 19:29
Thanks. This is not the whole query - that is in a string. I just dont know what to do with this part. — Mike
– Mike, Commented Jun 8, 2014 at 19:35
I have added the whole query in case this makes it easier to answer. — Mike
– Mike, Commented Jun 8, 2014 at 19:39

Gordon Linoff · Accepted Answer · 2014-06-08 19:51:17Z

1

Basically, you need single quotes around date constants. Here is a start:

INSERT INTO @temp(dateRange)
SELECT      ''' + @date0 + '''
UNION ALL
SELECT      ''' + @date1 + '''
UNION ALL
. . .

SQL is smart enough to be able to read a date that is written on the same system. Do note that if you were changing systems or writing this out to a file, you would want to be explicit about the date format (using convert() to put it in some canonical format).

answered Jun 8, 2014 at 19:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

That fixed it - seems I just missed two quotes each. Thanks a lot for the quick help and explanations !

Collectives™ on Stack Overflow

SQL Server: how to insert variables in dynamic Select

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related