Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: How to use an external table variable within dynamic SQL?

    I am writing some dynamic sql for some internal adhoc queries, and ran into issues when trying to call a table variable within the dynamic sql that was declared outside of it. Here is a simplified example:

    Code:
    DECLARE @t table (num int)
    INSERT INTO @t VALUES (1),(2),(3)
    
    DECLARE @sql nvarchar(MAX) = N'SELECT * FROM @t'
    exec(@sql)
    The throws a "Must declare table variable @t" error. Thoughts? (using 2008 R2)

    EDIT: I know I could use a temp table instead of a table variable, but now I am curious...
    Last edited by clawlan; 02-04-16 at 18:14.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Table variables (really like any other variable) only exist within the scope of the current batch.

  3. #3
    Join Date
    Jan 2016
    Posts
    24
    Provided Answers: 1
    In order to select FROM something you can run a query against a SELECT variable

  4. #4
    Join Date
    Nov 2003
    Posts
    167
    The table variable cannot be referenced out-of-scope. See blindman's comments on passing a table variable to sp_executesql here:
    http://www.dbforums.com/showthread.p...-sp_executesql

    Even a traditional temp table like #temp would be problematic here unless you use a global temp table (##temp).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •