CODEWITHSUNDEEP
t-sqlstored-procedurestemp-tables
JJ.
JJ.

Reputation: 9950

How do I know which temp table to delete if multiple stored procedures are creating temp tables with the same name?

I have been trying to figure this out the ENTIRE DAY :( ...

I have several stored procedures (in the same database as well as different databases) that do the same thing.

  1. Creates temp table with name X.
  2. Does processing with X.
  3. Drops X.

The problem is that these stored procedures are creating temp tables with the same name. How do I know which temp table to drop once I'm done with the processing if they all have the name and I can't really DROP using "LIKE" because a temp table might be being used by a different stored procedure?

Here's a scenario.

SP1 starts -

  1. Create temp table. ...and before it goes on, this happens:

SP2 is about finish

  1. Drop temp table.

If the above happens, SP1 runs into issues. Such as "temp table does not exist"

How do I bypass this issue?

When I go to drop a temp table, I need to make sure I'm dropping the table related to the stored procedure that created it. Is this even possible?

Upvotes: 0

Views: 529

Answers (1)

paparazzo
paparazzo

Reputation: 45096

You are trying to solve a problem you don't have. Just drop the table. If you look in SSMS you will really have unique tables. The SP knows which one to drop.

If SP1 and SP2 were using the same table you would have more problems than just drop.

  IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp
  CREATE TABLE #Temp (sID INT PRIMARY KEY CLUSTERED);
  -- look in SSMS and you will see #temp appended
  -- use #temp  
  IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp

In SP not sure you even need to drop. I think it will be dropped automatically.

But if you run the first two lines and look in SSMS you will see that you have your own #TEMP - not a shared #TEMP. Run the last line and you will see it go away.

Upvotes: 1

Related Questions