Error handling

Question

Error handling

cbarus

SSCommitted

Points: 1909
More actions
August 21, 2003 at 9:32 pm

#160001

We are developing an reasonably decent size application with 20 developers and 6 databases. 5 databases use a common database for configuration/common information. We need to have a good way for error handling. Just was wondering is there is any place I could get something like best practices for SQL Server error handling? Any help is greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

skrilla99 SSCertifiable Points: 5795 More actions · Answer 1

While I have never seen anything that discussed best practices, there are a few articles here on SSC that mention error handling... including

http://www.sqlservercentral.com/columnists/rmarda/doesyourspchokeonerrors.asp

There are a few others spread about the web...

http://www.sqlteam.com/item.asp?ItemID=208

and

http://www32.brinkster.com/srisamp/OTHArchives.asp

But I wonder if you are referring to the decision of where to trap your errors. ie - in your sql on in your application, etc...

Dan B

cbarus SSCommitted Points: 1909 More actions · Answer 2

HI Skrilla99-

Thanks for your time. I was talking about error handling in T-SQL and not in the application program. I went through the articles you sent me. I have one more question. Is there any way,I can trap an error message just like I can trap the error message. I need to trap the error message and return it back to the calling program.

Thanks for your time in advance

stax68 SSChampion Points: 11711 More actions · Answer 3

look at:

@@error

set xact_abort

transactions

sysmessages

in BOL

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 4

@@error is very useful in dealing with errors within a stored procedure.

However, I have found that if there is an error within a SP (whehter or not it is handled with @@error), ADO will report an error in the error object. As far as I know, there is nothing in SQL Server equivalent to the VB OnError Resume Next or Err.Clear.

If you are using ADO, you will need to check the error object for various connection errors so how would you differentiate between an error handled within a stored procedure and a connection error?

Jeremy