Temp table usage in dynamic sql

Question

Temp table usage in dynamic sql

sowmya.br

SSC Veteran

Points: 283
More actions
December 16, 2008 at 10:40 pm

#77161

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

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

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

Garadin One Orange Chip Points: 29613 More actions · Answer 2

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]

sowmya.br SSC Veteran Points: 283 More actions · Answer 3

Hi Pradeep,

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

sowmya.br SSC Veteran Points: 283 More actions · Answer 4

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

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 5

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.

ps. One Orange Chip Points: 29422 More actions · Answer 6

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

sowmya.br SSC Veteran Points: 283 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

ps. One Orange Chip Points: 29422 More actions · Answer 9

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

dongadoy SSC Eights! Points: 870 More actions · Answer 10

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

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

dongadoy SSC Eights! Points: 870 More actions · Answer 12

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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