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

Question

Post reply

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

nicolas.pages

SSC-Addicted

Points: 430
More actions
March 10, 2008 at 6:02 am

#179813

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!

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

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

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)

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 2

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

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

*/

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 3

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.

PaulBarbin SSC Veteran Points: 269 More actions · Answer 4

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

nicolas.pages SSC-Addicted Points: 430 More actions · Answer 5

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.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

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?

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 7

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)

Ian Yates SSCoach Points: 19738 More actions · Answer 8

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 🙂

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 9

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)

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 10

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?

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 11

Kent Waldrop

Hall of Fame

Points: 3064

March 11, 2008 at 5:47 am

#787877

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 12

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)

dragos_sv Default port Points: 1478 More actions · Answer 13

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'

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 14

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)