SQL Server: issue with declaring variable in dynamic SQL

Question

I created a dynamic stored procedure that starts as follows.

I can save this without errors but when I execute it it seems it doesn't recognise the @temp variable as it throws the following error: "Must declare the table variable "@temp"."

Could this be because of wrong quotes / escaping and if, how do I have to change this in order to have it right (I am pretty new to SQL so some of the quotes could be wrong or missing) ?

My SP (first part):

    @selection nvarchar(100)
AS
BEGIN

    SET NOCOUNT ON;

    BEGIN

    DECLARE @temp AS TABLE
    (
        ranking int,
        item nvarchar(100),
        groupCount int,
        groupName nvarchar(100)
    )

    DECLARE @sql nvarchar(max)

    SET @sql = '
    INSERT INTO @temp
    (       
                ranking,
                item,
                groupCount,
                groupName
    )
    SELECT      RANK() OVER(ORDER BY COUNT(*) desc, policy) [Rank],
                ' + @selection + ', 
                COUNT(*) AS groupCount,
                ''currentMonth'' AS groupName
    FROM        Log_PE 
    WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + ''01'', 112)
    GROUP BY    ' + @selection + '
    ORDER BY    groupCount desc, ' + @selection + '
        ...

Many thanks in advance for any help with this, Mike.

Solution

As I have already mentioned in comments section, Dynamic Sql has its own scope, any variable declared outside of that scope arent visible to dynamic sql, Your have to declare the variable inside your dynamic sql. Something like as follows....

SET @sql = N' DECLARE @temp AS TABLE
             (
              ranking int,
              item nvarchar(100),
              groupCount int,
              groupName nvarchar(100)
              )

INSERT INTO @temp
(       
            ranking,
            item,
            groupCount,
            groupName
)
SELECT      RANK() OVER(ORDER BY COUNT(*) desc, policy) [Rank],
            ' + @selection + ', 
            COUNT(*) AS groupCount,
            ''currentMonth'' AS groupName
FROM        Log_PE 
WHERE       CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + ''01'', 112)
GROUP BY    ' + @selection + '
ORDER BY    groupCount desc, ' + @selection + '

Comments

[CRAFTY DBA]

The problem with this solution is the table goes away. Either store as a #temp to be used further down the line for processing. Many of my utility scripts actually store results in small tables in tempdb.dbo.<name specific to user>

[Mike]

Thanks a lot for this as well !

[M.Ali]

Crafty he can select from this table further down within dynamic sql. out of dynamic's scope temp tables aren't visible either.

[CRAFTY DBA]

I would not put 5000 lines of code into a dynamic call. Local temp table is a better solution. I avoid table variables due to their scope is limited.

[CRAFTY DBA]

Also, look at Aaron Betrand's article on bad SQL habits. sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/… The convert on dateEsc makes the column not SARGABLE, no search index.

[M.Ali]

agreed :) , you can do a lot more with temp tables as compare to table variables.

[Mike]

One quick question: Where do I insert the second "if object..." ? Before I execute this ?

[Mike]

It turned out this is the best solution for me. Thanks again ! :)

[Youssef DAOUI]

you can use it to drop the temporary table at the end of your PS, if it worked please rate this as a valid answer :)

[Youssef DAOUI]

you can use it to drop the temporary table at the end of your PS, if it worked please rate this as a valid answer :)