The Temple of Fu

code, systems and games

If temp table exists – Drop it!

leave a comment »

You’ve created a temp table in a stored procedure and now you are testing it which involves dropping a temp table manually if you are not executing the stored procedure in it’s entirety (common if you are testing chunks of it).

So here’s the easy solution. We need to check if the temp table exists within the TempDB database and if it does, we need to drop it. Place the following at the top of your procedure, obviously before creating the temp table =] Now everytime it runs it will drop the table and then recreate it, no more manually dropping the temp table!

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

Replace #Temp with the name of your temp table.

Hope this helps!

Advertisements

Written by lordfu

July 29, 2010 at 1:50 pm

Posted in Database, MSSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: