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.

Dynamic Sql has its own SCOPE this table variable is not visible to your dynamic sql, Declare the table variable inside your dynamic sql. — M.Ali
– M.Ali, Commented Apr 2, 2014 at 19:01

M.Ali · Accepted Answer · 2014-04-02 19:11:24Z

1

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 + '

answered Apr 2, 2014 at 19:11

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

6 Comments

CRAFTY DBA Over a year ago

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>

M.Ali Over a year ago

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 Over a year ago

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 Over a year ago

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

M.Ali Over a year ago

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

|

Youssef DAOUI · Accepted Answer · 2014-04-02 19:07:08Z

0

Hope you are doing fine, well i think you may reconsider using a variable table because SQL won't understand what the @temp variable means when you will execute the EXEC(@sql), i recommand using a temp table instead of a variable table, here's the new code :

BEGIN

    SET NOCOUNT ON;

    BEGIN

if object_id('temp') is not null
  drop table temp

create table temp
    (
        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 + '

...

if object_id('temp') is not null
  drop table temp

I hope thiw will help you

answered Apr 2, 2014 at 19:07

Youssef DAOUI

3291 silver badge6 bronze badges

3 Comments

Mike Over a year ago

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

Youssef DAOUI Over a year ago

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 Over a year ago

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 :)

CRAFTY DBA · Accepted Answer · 2014-04-02 19:13:40Z

When you EXEC or sp_executesql, you get another name space (scope) for the process space. Change from a table variable to a local temp table, #temp.

Below is a quick example using sp_who2 illustrating use of a local temp table.

Sincerely

J

Code Snippet

-- CREATE LOCAL TABLE
CREATE TABLE #WHO2
(
  [spid] int not null
, [status] varchar (255) not null
, [login] varchar (255) not null
, [host_name] varchar (255) not null
, [blk_by] varchar(10) not null
, [db_name] varchar (255) null
, [command] varchar (255) not null
, [cpu_time] int not null
, [disk_io] int not null
, [last_batch] varchar (255) not null
, [program_name] varchar (255) null
, [spid2] int not null
, [request_id] int not null
); ;

-- DYNAMIC SQL      
DECLARE @VAR_TSQL VARCHAR(MAX);      
SET @VAR_TSQL = 'INSERT #WHO2 EXEC sp_who2';
EXECUTE (@VAR_TSQL);
GO

-- TABLE PERSISTS UNTIL SPID (CONNECTION) IS BROKEN
SELECT * FROM #WHO2

Collectives™ on Stack Overflow

SQL Server: issue with declaring variable in dynamic SQL

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related