dropping gloabl temporary table

Question

dropping gloabl temporary table

sql_2005_fan

SSCommitted

Points: 1871
More actions
October 5, 2006 at 10:21 am

#117195

I have a global temp table in on of the sp and instead of dropping it at the end of the Stored procedure I am trying to to drop it at the start of the procedure with the following command

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[##temp_test123]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[##temp_test123]

##temp_test123 is my gloabl temp table.

But its not dropping the table at the begining in case if couldn't drop at the end of the previous execution of the stores proecdure.

Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

Temp tables live in the tempdb database. So you could do this:

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[##temp_test123]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[##temp_test123]

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 2

sql_2005_fan

SSCommitted

Points: 1871

October 5, 2006 at 12:18 pm

#664411

Even this code is not dropping the table.

Thanks.

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 3

I tried the following code and it worked fine. Let me know if it's a correct way of doing it.

IF EXISTS( SELECT * FROM tempdb.dbo.sysobjects WHERE id=(SELECT id FROM tempdb.dbo.sysobjects WHERE name='##temp_test123'))

DROP TABLE ##temp_test123

Thanks.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

Sorry about that, I haven't been able to get the object_id function to work with the temp table either.

You could just do it like this:

IF

EXISTS( SELECT * FROM tempdb.dbo.sysobjects WHERE name='##temp_test123')

DROP TABLE ##temp_test123

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 5

One more question regarding these temporary tables.

I am creating a temp table in my stored procedure and droping at start of the next execution time.

What happens if two uses try to execute the Stored procedure at same time.

How can I handle this situation.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 6

You should probably not be using a global temp table. Does any process outside of the proc need to use the temp table? If not, change remove one # so that the table is not global. That way, each user will have their own copy of the table. I would also drop the table at the end of the proc.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 7

The sql query which creates the temp table is a dynalic sql statement and that the reason I have used the global temp table over local temp table.

Thanks.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 8

You probably need to come up with a unique name for the table everytime the proc runs and then drop the table at the end of the proc. Otherwise, if two users run the proc at the same time, they will bump into each other.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 9

how can I create an unique name for table everytime the procedure executed.

Thanks.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 10

It's easy enough to create a unique name, but now that I think about it, if the temp table has a unique name each time, then you'll have to use dynamic SQL for the rest of the proc, too, since you'll have to use the variable instead of the hard coded name.

How different can the table be each time the proc runs? It's possible to use IF statements to control how the table is built. Also, it is usually better to have more stored procedures that do different things than one-size-fits-all stored procs that try to do everything.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Sergiy SSC Guru Points: 110208 More actions · Answer 11

IF Object_Id('tempdb..##TempTable') IS NOT NULL

DROP TABLE ##TempTable

_____________
Code for TallyGenerator

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

Serqiy beat me to it! STOP USING SYSTEM TABLES FOR SUCH SIMPLE CHECKS!!! Use the system functions like Serqiy did.

--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)