global temp table

[week]

I am trying to delete a global temp table in the stored procedure.

I've used stmt like below but it doesn't work. I replaced #temp_table with ##temp_table but still doesn't work. Anybody knows what I should do?

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

Thanks.

 

[fluteplr]

I am not sure what you are trying to accomplish.

Local temp tables are dropped when the connection that created them is closed.

Global temp tables are dropped when the last connection that is accessing it is closed.

There would seem to be no reason to explicitly drop a temp table.

But I could be wrong, so could you explain in more detail what you are trying to do.

 

[week]

Ok, I should be working on the problem but I will explain to you why I am trying to drop a temp table. You know
global temp table is used differently than a temp table. This global temp table is used in an application that has series of procs linked one after another. The one who created in the procedure originally seemed didn't want to create a regular table for it but want to carry the temp table info to the next procedure. That's why it is created and we needed to clear the pre-existing temp table before it gets created.

I have few ideas if I can't drop the table within the proc. I might just replace the G-temp table with a regular table or have the user get out of the whole application which is what I think the user does anyway. I probably end up replacing the temp table with a regular table, if I can't explicitly drop it.

 

[week]

After reading what I responded, it seems why I am trying to keep the global temp table is missing. The user can stay in the same application and run it over and over again. When it hits the code where it creates the global temp table for the second time around, it will error out....so, I need to drop the existing global table and create a new one.

 

[fluteplr]

I think I found the problem. Temporary Tables global or otherwise are created in the tempdb.

Try something like this:

if exists (select * from tempdb.dbo.sysobjects where name = '##temp_table')
drop table ##temp_table

BTW I ran this in SQL 2000 results may vary in other versions.

 

[week]

Cool, that worked. Thanks a bunch!!