Can a temporary table created with an execute statement survive that statement?

  • Hi,

    If I create a temp table in a statement, like this :

    declare @statement

    set @statement = 'create #Table (column1 int null)'

    execute (@statement)

    Is it possible for the temporary table to survive that execute statement? I would like to force my table to do so but can't find anything about this on the web

    Please don't ask me why I create it in a statement.. : )

    Thanks!

  • Yes... but it it will be out of scope in the proc that has the exec statement... heh... please don't ask why 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff:

    What are the circumstances for that? When I run the following query it get the commented results:

    use tempdb

    go

    select name as before from sysobjects where type = 'U'

    exec ( 'create table #what(a int) select name as during from sysobjects where type = ''U'' ')

    select name as after from sysobjects where type = 'U' order by name

    /* -------- Sample Output: --------

    before

    ------------------------

    during

    --------------------------------------------------------------------------------------------------------------------------------

    #what_______________________________________________________________________________________________________________000000000005

    after

    ------------------------

    */

  • I'm not sure why you're trying to create a temp table in an exec statement, but I have some ideas because I used to do it myself. So I have workarounds.

    My tricks:

    If you're really trying to query data (i.e. "select ... into") and you already know the structure, make the temp table in the main session scope ( create table #mytemptable ... ) and fill it, which you can do, in the dynamic SQL (exec ('insert into #mytemptable ...').

    Alternatively, if you don't know the structure and are trying to figure it out programmatically, you can create the temp table in session with a dummy column, and issue dynamic ALTER TABLE commands (exec ('alter table #mytemptable ...'), then at the end, remove the original column.

  • Agreed that this is not normal or not normally good. But if you HAVE to, you also could use a Global temp table. Create table ##testing

    Paul

  • I create the table in a statement so I can insert a variable number of columns according a param's value. I don't feel like going into the 'Pivot' thing since the max number of columns is limited.

  • Not sure what would be so "unstable" or undesirable about it. It works the way it's supposed to. The global table variable doesn't seem to buy you much IMO.

    The procedure:

    - Create the temp table with the columns you know in the "main" statement.

    - run the 'alter table' command in one EXEC statement to add the variable columns

    - then, run the INSERT INTO statement in a second EXEC statement with the new columns.

    Once you're done - the original table will have all of the columns AND the data you want.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • kent waldrop (3/10/2008)


    Jeff:

    What are the circumstances for that? When I run the following query it get the commented results:

    use tempdb

    go

    select name as before from sysobjects where type = 'U'

    exec ( 'create table #what(a int) select name as during from sysobjects where type = ''U'' ')

    select name as after from sysobjects where type = 'U' order by name

    /* -------- Sample Output: --------

    before

    ------------------------

    during

    --------------------------------------------------------------------------------------------------------------------------------

    #what_______________________________________________________________________________________________________________000000000005

    after

    ------------------------

    */

    Because, by definition, temporary tables are scope sensitive by session and by proc. If you create it in the outer proc, sub-procs are in the same scope. If you create it in a sub-proc (EXEC qualifies as a subproc), then the temp table will not be available to the outer proc because there's a scope change. See Books Online for more information about scope sensitivety of temp tables...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The "alter table" within the exec statement idea is a good one that I hadn't thought of before 🙂

    One place where I've abused this practice is where we have MS Word calling a stored proc for mail merge data. Since the fields can vary slightly I have the proc determine a random global temporary table name of the form ##myTable_ with a newID() appended. That table name is then used in some dynamic SQL to do the work and then an exec statement is used to retrieve data from that table. Without the unique global temp table name you could have collisions with users executing the stored proc simultaneously.

    There are good reasons for MS SQL to have the temp tables well scoped and I doubt they're ever going to change 🙂

  • Giving away a fine "secret" here, but no one's ever heard of temporary stored procedures? Build it using dynamic SQL... execute it... it's all in the same scope that way. You can make temp tables with all sorts of variable column names that way... reporting on steriods... no RBAR... no global temp table or fixed table headaches.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Great suggestion Jeff!

    I don't suppose I can prefix the temp procedure name with a '#', so my "main" will need to drop it when done?

  • OK, Jeff, but that was not what I was asking.

  • Jim Russell (3/11/2008)


    Great suggestion Jeff!

    I don't suppose I can prefix the temp procedure name with a '#', so my "main" will need to drop it when done?

    Temporary stored procedures begin with a "#" and are treated the same way as Temp Tables so far as scope goes...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jim Russell (3/11/2008)


    Great suggestion Jeff!

    I don't suppose I can prefix the temp procedure name with a '#', so my "main" will need to drop it when done?

    yes you can

    check BOL for 'temporary stored procedures'

  • kent waldrop (3/11/2008)


    OK, Jeff, but that was not what I was asking.

    Thought I'd answered your question, Kent... what were you actually asking?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 38 total)

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