Post reply

Temp Tables and Dynamic SQL

  • November 15, 2005 at 6:18 am

    #167016

    Do people have any tips for working around the issue of having to use dynamic SQL and referencing temp tables?

  • November 15, 2005 at 6:32 am

    #603885

    If you're using temp tables in order to get values out of dynamic sql, look at sp_executesql. It lets you pass parameters to dynamic sql, both input and output parameters.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 15, 2005 at 6:53 am

    #603896

    I have data in a temp table and need to use dynamic sql to delete/insert data into the main table...

    I may be doing something wrong with the dynamic sql if so this is why I am using it.

    --

    I use the dynamic sql to select which database to write to...

    e.g "SELECT * FROM" + @DB_NAME + ".dbo.Table1"

  • November 15, 2005 at 7:12 am

    #603902

    Why do you not know what database the table is in?

    Could you expalin in a bit more detail the situation and what you're trying to do?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 15, 2005 at 7:39 am

    #603914

    There are several databases the user may wish to write to ... I understand it may sound convoluted but you will just have to trust me on that one there are firm reasons why this has had to be designed in this way....

    The simpilest way I can explain is to say I have data in a temp table that has been created from OPENXML that needs to be written to a table within a user specified database, so the only way I could think of this is by passing a parameter to the sproc.

    I just need a method or a workaround of using temptables with dynamic sql.

    I have tried using dynamic sql to construct the full sproc but concerned of going over the 4000 limit when xml files are involved

  • November 15, 2005 at 8:44 am

    #603950

    The easiest way about it is probably to create a global temporary table (##)

    then with the same stored procedure in each database call it as:

    exec dbname.dbo.procname @param1, @param2 ...

     


    * Noel

  • November 15, 2005 at 9:39 am

    #603969

    that was the fall back plan, but will require a sproc for each database, which I did not like the sound of... thanks for the help....

  • November 15, 2005 at 9:44 am

    #603972

    actually is very easy to do it. If you put it on the model all new databases will inherit the procedure but if the databases are already in place you are pretty much left with Dynamic sql or what I suggested.

    good luck


    * Noel

  • November 15, 2005 at 11:05 pm

    #604091

    Why a gobal temporary table?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply