Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Exclamation Unanswered: How to pass table variable to sp_executesql?

    Hi ,
    I am trying to do the following:


    begin
    declare @tvar Table
    (
    job_id smallint,
    job_desc varchar(50),
    min_lvl tinyint,
    max_lvl tinyint
    )

    insert into @tvar
    select * from jobs

    --exec sp_executesql N'select * from @tvarname order by @ord',N'@tvarname TABLE, @ord varchar(50)',@tvar,'job_id desc'
    exec sp_executesql N'select * from @tvar order by @ord',N'@ord varchar(50),@tvar table','job_id desc',@tvar
    end


    i am getting the following error:
    Server: Msg 137, Level 15, State 2, Line 15
    Must declare the variable '@tvar'.

    Any suggestions?

    Thx in advance.
    -tabrez

  2. #2
    Join Date
    Jun 2003
    Posts
    4
    You cannot use variables in place of object names. You must use dynamic SQL here:

    DECLARE @sql varchar(200)

    SET @sql = 'SELECT * INTO ' + @tvar + ' FROM jobs'
    EXEC(@sql)

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Check SP_EXECUTESQL topic from books online for code example.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Jun 2003
    Posts
    4
    Originally posted by Flatlander
    You cannot use variables in place of object names. You must use dynamic SQL here:

    DECLARE @sql varchar(200)

    SET @sql = 'SELECT * INTO ' + @tvar + ' FROM jobs'
    EXEC(@sql)
    sp_executesql does allow you to pass the definition and values of variables. However, I was not able to do that for a table variable. any ideas??

  5. #5
    Join Date
    Jun 2003
    Posts
    4
    I could be misunderstanding, but given your code listing above, I don't think it's the sp_execsql that's erroring out on you. I think it's this part:

    insert into @tvar
    select * from jobs
    This looks like where you're trying to use a scalar variable, when SQL Server wants an object.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, you can use your table variable in place of an object name, but when you use execute, your current variables go out of scope. A dynamic query will not help either, because once again your table variable does not exist in environment where your exec statement is operating.

    The solution is to use a temporary table instead, because it is available throughout the life of your connection. You will need to clean up after at the end of your procedure.

    blindman

  7. #7
    Join Date
    Jun 2003
    Posts
    4
    Originally posted by blindman
    Yes, you can use your table variable in place of an object name, but when you use execute, your current variables go out of scope. A dynamic query will not help either, because once again your table variable does not exist in environment where your exec statement is operating.

    The solution is to use a temporary table instead, because it is available throughout the life of your connection. You will need to clean up after at the end of your procedure.

    blindman
    Hi,
    Thx for the reply. I am trying to use sp_executesql in tandem with tablevariable to avoid recompiles. If I use a temp table again I will have recompiles. Any suggestions on that end?

    Thx
    Tabrez

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you use sp_execute I think the optimizer will have to compile a new plan each time regardless of whether you use a temp table or a table variable. Although the 2000 optimizer might be smart enough to find an already cached plan, once again it could use the plan regardless of whether it was operating on a table variable or a temporary table.

    The big advantabe you get with table variables over temporary tables is speed. A table variable exists soley in memory and is not written to disk like a temporary table (OK, if it's big enough some of it may have to be cached to disk...).

    If you maintain good statistics on your tables a recompile shouldn't take fractions of a second anyway, so unless your code is being repeated hundreds of times per hour it should't make a big difference in performance.

    blindman

Posting Permissions

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