Error handling in T SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error handling in T SQL

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-21 : 13:37:36

hello,
Whats the best way to check for errors in a SP and to debug, especially on the fly ?

Anyone got any pointers advice ???

Afrika

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-21 : 13:40:13

In v2005 you can create a try catch block around the outermost call and trap errors there - but you will have problems knowing exactly where the error occurred.
In v2000 you need to check @@error after every statement.
In both cases not all errors are trappable and some will terminate the connection.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-05-21 : 13:53:32

Plus you can use

SET XACT_ABORT ON

to force errors to abort the SProc - in case you miss something!

Still doesn't catch an EXEC to a secondary Sproc that fails :-(

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-21 : 18:28:08

Thanks Nigel, Kristen,
am actually using 2000.

Am trying to debug a SP that signs up users and check if thier already have accounts. Its like mouse traps in a dark old house

i would add the @@error to see what the exact error is.

It works well, but throws and execption when a user enters and existing phone number.

brb........

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-21 : 18:41:28

Oh i would also read up on SET XACT_ABORT ON
cheers Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-21 : 19:08:42

Check to see if there are any triggers involved too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-21 : 19:39:26

>> Check to see if there are any triggers involved too.
none

Hey ! Nigel its 12.36am, what are you still doing online ???