Temp table usage in dynamic sql

  • Hi All,

    I have a problem using temperory table in dynamic TSQL

    I am building one sql statement dynamically through stored procedure parameters and then trying to use this sql result to insert into temp table.(#temp). but the scope of has table is lost after it comes out of the sql and when i do select * from #temp after tsl, it throws error.

    is there any work around for this.

    Please suggest

  • Refer http://www.sqlservercentral.com/articles/Basic+Querying/temptablesinsqlserver/1279/

    If number of connections is 1, even the global temp table wont be of much help as the table will be dropped if that connection is lost.

    You can create a permanent table and drop it once you no longer need it. I'm sure there must be better ways to deal with this.



    Pradeep Singh

  • You can do it, you just need to create your temp table explicity, and not using a 'select into' from the dynamic sql (as it's not in the scope of your parent connection if you do it that way).

    Consider the following simple example:

    [font="Courier New"]CREATE PROCEDURE sp_a

                AS

    SELECT 'YaySP'

    GO

    CREATE TABLE #a(

                    a    VARCHAR(5))

    DECLARE @sql VARCHAR(500)

        SET @sql = 'INSERT INTO #a(a) VALUES(''Yay'')'

    EXEC (@sql)

        SET @sql = 'insert into #a(a) exec sp_a'

    EXEC (@sql)

    SELECT *

        FROM #a

    DROP TABLE #a

    DROP PROCEDURE sp_a

                [/font]

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Hi Pradeep,

    As it runs in widnwos authentication, if i create physical table and tehn try to drop it , issue with permisson will arise

  • Hi Seth

    i tried this but after exec of sql, when i try to do select * from $a, it throws error saying #a does not exists in database. The session of that table is lost after exec stmt

  • sowmya.br (12/16/2008)


    Hi All,

    I have a problem using temperory table in dynamic TSQL

    I am building one sql statement dynamically through stored procedure parameters and then trying to use this sql result to insert into temp table.(#temp). but the scope of has table is lost after it comes out of the sql and when i do select * from #temp after tsl, it throws error.

    is there any work around for this.

    Please suggest

    Actually temp. table life time is session wise, you can't access temp table created in procedure not available outside of procedure.

    Could you please post your procedure, so that will try to help.

  • sowmya.br (12/16/2008)


    Hi Seth

    i tried this but after exec of sql, when i try to do select * from $a, it throws error saying #a does not exists in database. The session of that table is lost after exec stmt

    I just ran that code written by Seth and got this result.

    a

    -----

    Yay

    YaySP



    Pradeep Singh

  • Everything is fine in the SP which seth has given

    Only thing is i donot want to create #a like below

    CREATE TABLE #a(

    a VARCHAR(5))

    instead it has to do dynamic like select * into #a because the values i am inserting is like copying of different tables @ run time, so i will not be knowing the number of oculns prior.

  • If you create the temp table in dynamic SQL, the temp table will go out of scope and be dropped as soon as the dynamic SQL ends. There's no way around that, not even using global temp tables

    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
  • Everything is fine in the SP which seth has given

    Only thing is i donot want to create #a like below

    CREATE TABLE #a(

    a VARCHAR(5))

    instead it has to do dynamic like select * into #a because the values i am inserting is like copying of different tables @ run time, so i will not be knowing the number of oculns prior.

    you can still write insert into statement for this...(without using SELECT INTO)

    INSERT INTO #a(a) SELECT TOP 10 ID FROM testTable



    Pradeep Singh

  • sowmya.br (12/16/2008)


    Everything is fine in the SP which seth has given

    Only thing is i donot want to create #a like below

    CREATE TABLE #a(

    a VARCHAR(5))

    instead it has to do dynamic like select * into #a because the values i am inserting is like copying of different tables @ run time, so i will not be knowing the number of oculns prior.

    All the work you need to do with the temp table needs to be included in the dynamic SQL.

    Even if you were able to reference the temp table outside the dynamic SQL, what can you do with it without knowing the columns?

    Maybe if you tell us more of your process, someone can suggest an alternative.

  • GilaMonster (12/17/2008)


    If you create the temp table in dynamic SQL, the temp table will go out of scope and be dropped as soon as the dynamic SQL ends. There's no way around that, not even using global temp tables

    This fails as expected with "Server: Msg 208, Level 16, State 1, Line 4

    Invalid object name '#Temp'."

    DECLARE @sql VARCHAR(200)

    SET @sql = 'SELECT TOP 10 * INTO #Temp FROM master.dbo.syscolumns'

    EXEC(@SQL)

    SELECT * FROM #Temp

    This works...

    DECLARE @sql VARCHAR(200)

    SET @sql = 'SELECT TOP 10 * INTO ##Temp FROM master.dbo.syscolumns'

    EXEC(@SQL)

    SELECT * FROM ##Temp

    .. the global temp table is available outside the scope of the dynamic sql.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Yes, a global temp table is an option. Just have to remember the concurrency issue - can't have multiple processes executing the code concurrently, or have different code using the same temp table name.

  • dongadoy (12/18/2008)


    Yes, a global temp table is an option. Just have to remember the concurrency issue - can't have multiple processes executing the code concurrently, or have different code using the same temp table name.

    An important point! I use this trick for ETL only.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris Morris (12/18/2008)


    .. the global temp table is available outside the scope of the dynamic sql.

    Ah. I thought that the end of the dynamic SQL would mean that the last reference to the global temp table was gone.

    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 15 posts - 1 through 15 (of 15 total)

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