Error trapping in stored procedure

Error trapping in stored procedure

Post by Erin » Fri, 28 Feb 2003 23:37:21



I am creating a temp table at the beginning of a stored
procedure and dropping it at the end (I can't use #temp
because I am doing dynamic SQL with the execute command).  
My problem is if for some reason the stored procedure
fails the first time, then the table doesn't get dropped.  
I end up having to manually drop it.
I want to put a line at the beginning of the stored
procedure to drop the temp table if it exists.
How would I check to see if the table exists?
Or how could I turn off error trapping so that if the drop
table fails, it wouldn't care?
 
 
 

Error trapping in stored procedure

Post by Tibor Karasz » Fri, 28 Feb 2003 23:57:23


if object_id('tempdb..##tmp') IS NULL
PRINT 'doesn''t exist'
ELSE
PRINT 'exists'

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, give us something we can execute in Query Analyzer


> I am creating a temp table at the beginning of a stored
> procedure and dropping it at the end (I can't use #temp
> because I am doing dynamic SQL with the execute command).
> My problem is if for some reason the stored procedure
> fails the first time, then the table doesn't get dropped.
> I end up having to manually drop it.
> I want to put a line at the beginning of the stored
> procedure to drop the temp table if it exists.
> How would I check to see if the table exists?
> Or how could I turn off error trapping so that if the drop
> table fails, it wouldn't care?


 
 
 

Error trapping in stored procedure

Post by Greg Linwoo » Sat, 01 Mar 2003 16:41:03


Hi Erin.

I know it's not directly answering the question, but if you're on SQL 2000,

automatically cleaned up at the end of scope (eg stored procedure / batch).
They also have less logging overhead than temp tables and can in many cases
run considerably faster than temp tables.

Regards,
Greg Linwood


Quote:> I am creating a temp table at the beginning of a stored
> procedure and dropping it at the end (I can't use #temp
> because I am doing dynamic SQL with the execute command).
> My problem is if for some reason the stored procedure
> fails the first time, then the table doesn't get dropped.
> I end up having to manually drop it.
> I want to put a line at the beginning of the stored
> procedure to drop the temp table if it exists.
> How would I check to see if the table exists?
> Or how could I turn off error trapping so that if the drop
> table fails, it wouldn't care?