Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    USA
    Posts
    5

    Unanswered: Dynamic SQL and Table-type Variable

    Hello,

    Here's goes my problem - I have a stored procedure which basically queries a Index Server (file system) catalog. The SP utilizes the SQL Server 2000 table type variable for temporary processing. I'm trying to use the table variable inside a dynamic SQL query but I keep getting the error: Must declare the variable '@TempTable'

    The SP is:


    DECLARE @TempTable TABLE
    (
    ID int IDENTITY PRIMARY KEY,
    MyFileName nvarchar (100),
    MyFilePath nvarchar (150)
    )

    DECLARE @qs nvarchar(4000)

    SELECT @qs = 'INSERT INTO @TempTable
    (
    Name,
    Category,
    )
    SELECT * FROM OpenQuery(MyIndexServerCatalog,
    ''SELECT FileName, Path FROM SCOPE()
    WHERE CONTAINS('''' ' + @Keystring + ' '''')''
    )
    AS Q'

    exec (@qs)


    Note: @Keystring is an input parameter in the SP.

    Please assist. Thanks for your time and efforts.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    try this:

    Code:
    SELECT @qs = 'SELECT * FROM OpenQuery(MyIndexServerCatalog, ''SELECT FileName, Path 
                                                                    FROM SCOPE() 
                                                                   WHERE CONTAINS('''' ' + @Keystring + ' '''')'' ) AS Q'
    
    INSERT INTO @TempTable (Name,Category)
    exec (@qs)
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    by the way, I don't see any reason to use a table-type variable in place of a # or ## temp table. Both tables are created in the tempdb. ANyone have a different view point?
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Feb 2003
    Location
    USA
    Posts
    5
    Thanks for your reply.

    1. This won't work: INSERT INTO table_variable EXEC stored_procedure

    2. Also, " ... When you create a temporary table (#TABLE) it physically creates the table in tempdb. This creates overhead. When you create a table variable it only resides in memory which means it's much faster. ..."

    http://www.sqlteam.com/item.asp?ItemID=9454

  5. #5
    Join Date
    May 2002
    Posts
    299
    Originally posted by Paul Young
    by the way, I don't see any reason to use a table-type variable in place of a # or ## temp table. Both tables are created in the tempdb. ANyone have a different view point?
    1. Temporary tables can be global or local while table variables are
    always local to its scope whether it is a function, Sp etc.
    2. Temporary tables can be destroyed explicitly using a drop statement,
    while you cannot release a table variable unless it goes out of scope.
    3. Temporary variables are created in tempdb database while table
    variables are created in Server Memory but do physically I/O out to tempdb due to memory constraint.
    4. You can copy one temporary table to another while you cannot assign a table variable to another.
    5. Table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.
    6. Transactions involving table variables last only for the duration of an
    update on the table variable. Thus, table variables require less locking
    and logging resources.
    7. http://support.microsoft.com/?id=305977

    The bottom line is considering using table variables instead of temporary tables. Temporary tables are useful in cases when indexes need to be created explicitly on them, or when the table values need to be visible across multiple stored procedures or functions. In general, table variables contribute to more efficient query processing. (From SQL Server Books Online)
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

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