Error trapping in stored procedure 
Author Message
 Error trapping in stored procedure

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?


Mon, 15 Aug 2005 22:37:21 GMT
 Error trapping in stored procedure

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

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?



Mon, 15 Aug 2005 22:57:23 GMT
 Error trapping in stored procedure
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?



Tue, 16 Aug 2005 15:41:03 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Error trapping in stored procedure

2. Error trapping in Stored Procedure

3. Trap Entended stored procedure errors in DBLIB

4. DTS with stored procedures / error trapping

5. Trapping Errors Raised Within Stored Procedures

6. trapping errors in stored procedures (newbie)

7. Trapping errors from stored procedure calls

8. Trapping error handles in extended stored procedures

9. Trapping and processing errors within stored procedures (SQL 7.0)

10. Stored Procedure error trap

11. Stored procedure error trapping from ASP

12. Error trapping in a stored procedure?


 
Powered by phpBB® Forum Software