locked
Table Variable and Dynamic SQL RRS feed

  • Question

  • Hi All, 

    I have a temporary table in my stored procedure. I am using dynamic sql to update it in the stored procedure.
    Instead of temporary table I want to use a 'table variable' but dynamic sql is not working along with table variable
    (I get error message saying that the table variable needs to be declared though i declared it already.I think it is a scope issue).

    Is there any work around for table variable and dynamic sql.Thanks a lot

    Wednesday, April 4, 2012 4:58 PM

Answers

  • No, there is no workaround unless you're using SQL Server 2008 and up and want to pass this table as a READONLY parameter

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:21 PM
    Wednesday, April 4, 2012 5:02 PM
  • There is a way to that. You have to define your TABLE VARIABLE inside your dynamic SQL not outside of that. 

    Declare @strSQL nVARCHAR(1000)

    Set @strSQL = 'Declare @a Table (ID Int) Insert Into @a Select ID From dbo.Employee Select * From @a'


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:21 PM
    Wednesday, April 4, 2012 5:04 PM
  • There is a way to that. You have to define your TABLE VARIABLE inside your dynamic SQL not outside of that. 

    Declare @strSQL nVARCHAR(1000)

    Set @strSQL = 'Declare @a Table (ID Int) Insert Into @a Select ID From dbo.Employee Select * From @a'


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    But the table variable is not accessable from outside the dynamic SQL so it is useless

    Chuck

    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:21 PM
    Wednesday, April 4, 2012 5:10 PM
  • Yes, the dynamic SQL is not part of the procedure, but constitutes a scope of its own. You need to stick with your temp table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:26 PM
    Wednesday, April 4, 2012 10:20 PM
  • Kalman, 

    It is correct, it cannot be accessed from outside and cannot communicate between parent and child process. But it is possible to define that inside a dynamic SQL and use it. 

    Unfortunately, it is NOT clear what he/she was trying to do. Maybe there would be some cases that it make sense to do something like that. 

    As you saw in the sample, you can use it but that table variable will NOT be accessible from outside of your Dynamic SQL.

    Use AdventureWorks
    Go
    Declare @strSQL Nvarchar(1000) 
    Set @strSQL = 'Declare @myTable Table (EmployeeID Int, Title Nvarchar(100)) Insert Into @myTable Select EmployeeID, Title From AdventureWorks.HumanResources.Employee Where HireDate <= ''' + '1998/01/01''' + ' Select * From @myTable'
    Exec sp_ExecuteSQL @strSQL 
    
    --output
    EmployeeID	Title
    1	Production Technician - WC60
    2	Marketing Assistant
    3	Engineering Manager


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    Tuesday, April 17, 2012 6:55 PM

All replies

  • Nope

    Chuck

    Wednesday, April 4, 2012 5:01 PM
  • No, there is no workaround unless you're using SQL Server 2008 and up and want to pass this table as a READONLY parameter

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:21 PM
    Wednesday, April 4, 2012 5:02 PM
  • There is a way to that. You have to define your TABLE VARIABLE inside your dynamic SQL not outside of that. 

    Declare @strSQL nVARCHAR(1000)

    Set @strSQL = 'Declare @a Table (ID Int) Insert Into @a Select ID From dbo.Employee Select * From @a'


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:21 PM
    Wednesday, April 4, 2012 5:04 PM
  • There is a way to that. You have to define your TABLE VARIABLE inside your dynamic SQL not outside of that. 

    Declare @strSQL nVARCHAR(1000)

    Set @strSQL = 'Declare @a Table (ID Int) Insert Into @a Select ID From dbo.Employee Select * From @a'


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    But the table variable is not accessable from outside the dynamic SQL so it is useless

    Chuck

    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:21 PM
    Wednesday, April 4, 2012 5:10 PM
  • There is a way to that. You have to define your TABLE VARIABLE inside your dynamic SQL not outside of that. 

    Declare @strSQL nVARCHAR(1000)

    Set @strSQL = 'Declare @a Table (ID Int) Insert Into @a Select ID From dbo.Employee Select * From @a'


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    But the table variable is not accessable from outside the dynamic SQL so it is useless

    Chuck

    If you PUT that inside your SQL statement is accessible. 

    Use AdventureWorks
    Go
    Declare @strSQL Nvarchar(1000) 
    Set @strSQL = 'Declare @myTable Table (EmployeeID Int, Title Nvarchar(100)) Insert Into @myTable Select EmployeeID, Title From AdventureWorks.HumanResources.Employee Where HireDate <= ''' + '1998/01/01''' + ' Select * From @myTable'
    Exec sp_ExecuteSQL @strSQL 
    
    --output
    EmployeeID	Title
    1	Production Technician - WC60
    2	Marketing Assistant
    3	Engineering Manager


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Wednesday, April 4, 2012 5:15 PM
  • OK - but to what end?  If that is really what he was trying to do, he would just select from the table.

    I'm having a hard time coming up with a situation where defining a table variable inside a dynamic script would be needed.


    Chuck

    Wednesday, April 4, 2012 5:19 PM
  • Yes, the dynamic SQL is not part of the procedure, but constitutes a scope of its own. You need to stick with your temp table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Tuesday, April 17, 2012 6:26 PM
    Wednesday, April 4, 2012 10:20 PM
  • Is there any work around for table variable and dynamic sql.Thanks a lot

    You have to use #temptable, ##globaltemptable or regular (work) table.

    Related article:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth SQL SERVER & BI TRAINING

    Wednesday, April 11, 2012 6:59 PM
  • >If you PUT that inside your SQL statement is accessible.

    Arbi - Am I missing something?  @tablevariable cannot be used to communicate between dynamic SQL parent and child processes. Thanks.


    Kalman Toth SQL SERVER & BI TRAINING





    • Edited by Kalman Toth Tuesday, April 17, 2012 7:00 PM
    Tuesday, April 17, 2012 6:23 PM
  • Kalman, 

    It is correct, it cannot be accessed from outside and cannot communicate between parent and child process. But it is possible to define that inside a dynamic SQL and use it. 

    Unfortunately, it is NOT clear what he/she was trying to do. Maybe there would be some cases that it make sense to do something like that. 

    As you saw in the sample, you can use it but that table variable will NOT be accessible from outside of your Dynamic SQL.

    Use AdventureWorks
    Go
    Declare @strSQL Nvarchar(1000) 
    Set @strSQL = 'Declare @myTable Table (EmployeeID Int, Title Nvarchar(100)) Insert Into @myTable Select EmployeeID, Title From AdventureWorks.HumanResources.Employee Where HireDate <= ''' + '1998/01/01''' + ' Select * From @myTable'
    Exec sp_ExecuteSQL @strSQL 
    
    --output
    EmployeeID	Title
    1	Production Technician - WC60
    2	Marketing Assistant
    3	Engineering Manager


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    Tuesday, April 17, 2012 6:55 PM