Variable based name for temp table during SP

Article
08/25/2010

Question

_{Wednesday, August 25, 2010 9:19 PM}

Hello All,

I have a question regarding variables and executing SQL code mostly.

My problem is that I want to name my temp tables with different names (#Temp1, #Temp2, #Temp3, ...) this is done dynamically.

Now I have a piece of code that declares a tableName varchar and then sets it to be #Temp1, the problem is I cannot just write an SQL statement during SP using that variable, from a lookup around the net I need to use sp_executesql, but executesql runs in a different session and then my temporary table is not created in my session and I cannot do operations on it.

How can I solve this please?

Gil

All replies (21)

_{Thursday, August 26, 2010 7:56 AM ✅Answered | 1 vote}

check this code. this might need little verification

create

procedure

test

@intnumber

int

begin

Declare

@vLoop

int

set

@vLoop = @intnumber

declare

@vTableQuery nvarchar(500)

declare

@vRecQuery nvarchar(250)

-- loop to create and insert

while

@vLoop >

begin

set @vTableQuery =

'create table abc' + cast(@vLoop as varchar(5))+

(

id int,name varchar(50)

);'

exec sp_executesql

@vTableQuery

-- now insert records

set @vRecQuery =

'Insert into abc' + cast(@vLoop as varchar(5))+

values (1,'' shamas '');'

set @vLoop = @vLoop -

end

set

@vLoop = @intnumber

-- loop to delete

while

@vLoop >

begin

set @vTableQuery =

'select * from abc' + cast(@vLoop as varchar(5))+ ';'

exec sp_executesql

@vTableQuery

set @vLoop = @vLoop -

end

set

@vLoop = @intnumber

-- loop to delete

while

@vLoop >

begin

set @vTableQuery =

'drop table abc' + cast(@vLoop as varchar(5))+ ';'

exec sp_executesql

@vTableQuery

set @vLoop = @vLoop -

end

--exec test 2

Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

_{Wednesday, August 25, 2010 10:04 PM}

GilPeleg:

Yes you are right that you need to use sp_executesql, but you need to use it with OUTPUT parameter there you are going to store output value.

Please refer http://support.microsoft.com/kb/262499/en-us link.

Thanks!!

_{Wednesday, August 25, 2010 10:16 PM}

Can you please elaborate more on your question?

If you create your temp table and then call some query with execute sp_ExecuteSQL, then the temp table will be visible.

You can not access the temp table created as a result of sp_ExecuteSQL back in the calling procedure.

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Wednesday, August 25, 2010 10:26 PM}

My problem is that I want to name my temp tables with different names (#Temp1, #Temp2, #Temp3, ...) this is done dynamically.

Now I have a piece of code that declares a tableName varchar and then sets it to be #Temp1, the problem is I cannot just write an SQL statement during SP using that variable, from a lookup around the net I need to use sp_executesql, but executesql runs in a different session and then my temporary table is not created in my session and I cannot do operations on it.

Tell us what you are really doing instead. This sounds like you took the wrong road on your way out of town, and you are now lost in the wilderness.

Why do you want different names of your temp tables? Temp-tables are unique to the process anyway, so there can be no conflict between different processes.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

_{Thursday, August 26, 2010 5:14 AM}

Hey,

Naom, the problem is, I don't know what my current table name is, it can be #Temp1, #Temp2, etc so I need to use my variable to create that table too, so I cannot do it in a direct query but through an executesql.

Erland, I am implementing a recursive select in MSSQL 2000, I have n levels so I cannot do just left join, it needs to be 'recursive' in a way.

I am selecting all my roots rows into #Temp table, then I get all their children into #Temp1 table, and insert #Temp1 result into #Temp, then I get all the children of #Temp1 into #Temp2, then I insert #Temp2 to #Temp... and returns till I get no result in #TempN.

So I need to use variables for my table names, and I can't find a solution other than global temp tables, it might be good enough for me cause I have only one connection to my database, so i'll ask about it too, is it DB specific or is a global temp table per the entire MSSQL?

I can have multi DBs that execute this recursive select, but only one connection per DB, so if global tables are per DB, it might be good enough for me.

Thanks for the assistance so far, and looking forwad for your replies.

Gil

_{Thursday, August 26, 2010 5:50 AM}

If you need to recurse through the data I would take a look at CTEs (Common Table Expressions). If you really are set on creating dynamic Temp Tables then you would need to use a Global temp Table (##Table) Instead of (#Table) so that you can access the table.

So for example:

DECLARE @SQLTable NVARCHAR(200);

SET @SQLTable = 'CREATE TABLE ##Table( 
       ID INT)';

exec sp_executesql @SQLTable;

Insert Into ##Table
Values(1);

Select *
from ##Table
Drop Table ##Table;

If you use the Global Temp table you should be able to access it. I know it works in 2008...just did it the other day.

--B

_{Thursday, August 26, 2010 6:19 AM | 1 vote}

What is the Maximum level of nesting? If its defined, try to make it with If else statements.

Select * from #tbl1 -- Master
if Exists(Select * from #tbl2)
begin
    Select * from #tbl2
end

if Exists(Select * from #tbl3)
begin
    Select * from #tbl3
END
.
.
.
.

And do not go for dynamic sql.

Also check the following link. It might give you some clue to do what you want to do....

http://www.mssqltips.com/tip.asp?tip=938

My Blog

_{Thursday, August 26, 2010 6:20 AM}

Ben, thank you for your reply, but as I stated, I am looking to do this in MSSQL 2000, CTEs doesn't exist yet.

Global temp tables are a good possibilities and it seems to work well too, my only question is, are they per DB or per the entire MSSQL Server?

_{Thursday, August 26, 2010 6:28 AM}

Hi Gil,

I think Global table scrope depends on user rights that open instance.

Read the following link

http://decipherinfosys.wordpress.com/2007/05/04/temporary-tables-ms-sql-server/

Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

_{Thursday, August 26, 2010 6:45 AM}

Hey,

Thank you for all your assistance, it works with global temp tables, I do not know the nest level of my recursion so I must do it dynamically.

Regarding the global temp tables, any idea how can I set it so it will be per DB and not per MSSQL instance? Theres nothing that explains it in the link.

Thanks!

Gil

_{Thursday, August 26, 2010 6:47 AM}

You can create user and assign only msdb and one required database to that user. When you connect with that user you will get global table with its instance only.

If helpfull please mark as answer

Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

_{Thursday, August 26, 2010 6:50 AM}

There is one other solution for your problem if you like.

create physical tables as many as you need at start of your procedure dynamically. do you work what ever you want and delete tables at the end of the procedure.

This might looks odd but it helps.

Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

_{Thursday, August 26, 2010 7:06 AM}

The first one is problematic, since I don't want to create a user for every db I have, is there another solution?

Regarding the second post I don't know how many tables i'll need so I need to create them dynamically.

I am encountering another issue with the global temp tables, it seems they aren't being deleted at the end of the stored procedure, am I doing anything wrong? Is there any way I can do this?

If not, is there any solution to my starting point with local temporary tables?

Another option that might be good for me... is the session of executesql always the same one? If yes I can just keep doing all my queries on it? and it'll help me with what I want? (Meaning between two execeutesql queries it stays the same session and same local tables)

Thanks for the assistance,

Gil.

_{Thursday, August 26, 2010 7:23 AM}

Ok I know that the answer to my "Another option" is no, since in order to keep the local temp table I need to create it first through regular query, what I cannot do because the table name is stored in a varaible... so i'm stuck again, anyone have another solution for me?

Thanks for all the help.

_{Thursday, August 26, 2010 7:26 AM}

I still stand with my second solution because you can estimate physical table numbers same as you are now doing with temp table.

send me your procedure code and i will update it for you.

if you will send attachment use my mail shamas@intelligentsiasoftware.com.

for you temp table deletion option. if session is open you might need to use drop table statement.

Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

_{Thursday, August 26, 2010 7:41 AM | 1 vote}

> Erland, I am implementing a recursive select in MSSQL 2000, I have n levels so I cannot do just left join, it needs to be 'recursive' in a way.

Use a single table, with the nestlevel as one of the key columns. Probably there is no need for dynamic SQL at all.

_{Thursday, August 26, 2010 7:41 AM}

What do you mean by 'I still stand with my second solution because you can estimate physical table numbers same as you are now doing with temp table.' ?

How will using a physical table make a change? I will still need to create it prior to my loop and I don't know how many tables to create...

If it's something I can do easily i'd be happy for an example or something, because my code is in an internal dev network and it's not easy to get things copied between... heh.

Thanks,

Gil

_{Thursday, August 26, 2010 8:14 AM}

Alright, this is working, THANK YOU very much! While it may not be the ideal solution, it works :)

Good day!

_{Thursday, August 26, 2010 8:25 AM}

Thanks. have a nice night :) Please mark as Answer

there is another change in procedure. I have used drop statement with if exists in start of procedure

create

procedure

test

@intnumber

int

begin

Declare

@vLoop

int

declare

@vTableQuery nvarchar(500)

declare

@vRecQuery nvarchar(250)

set

@vLoop = @intnumber

-- loop to delete

while

@vLoop >

begin

set @vTableQuery =

'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''abc'+cast(@vLoop as varchar(5)) +

''') AND type in (N''U''))

DROP TABLE abc'

+cast(@vLoop as varchar(5)) + ';'

exec sp_executesql

@vTableQuery

set @vLoop = @vLoop -

end

set

@vLoop = @intnumber

set

@vTableQuery = ''

-- loop to create and insert

while

@vLoop >

begin

set @vTableQuery =

'create table abc' + cast(@vLoop as varchar(5))+

(

id int,name varchar(50)

);'

exec sp_executesql

@vTableQuery

-- now insert records

set @vRecQuery =

'Insert into abc' + cast(@vLoop as varchar(5))+

values (1,'' shamas '');'

set @vLoop = @vLoop -

end

set

@vLoop = @intnumber

-- loop to delete

while

@vLoop >

begin

set @vTableQuery =

'select * from abc' + cast(@vLoop as varchar(5))+ ';'

exec sp_executesql

@vTableQuery

set @vLoop = @vLoop -

end

--exec test 2

Shamas Saeed (if Post helpful please mark as Answer) MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

_{Thursday, August 26, 2010 10:40 AM}

> Thanks. have a nice night :) Please mark as Answer

No, please don't, because it is the wrong way. Instead of making the loop number part of the table name, make it a column in the table, and all GilPeleg needs is one single table. And probably no dynamic SQL at all.

_{Thursday, August 26, 2010 12:59 PM}

Do a simple google search on "recursive query SQL 2000".

Here is one sample I know

http://www.sqlusa.com/bestpractices/training/scripts/recursivecte/

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Connect, code, and grow

Share via

Question

All replies (21)

Share via

Variable based name for temp table during SP

Question

All replies (21)

Additional resources