Error handling in Stored Procedures

I had an intrview today and the interviewer asked me about Error handling in SQL Server 2000. I am afraid to say that i could not answer as i had ignored this aspect for a long time. So today i decided to get my head around Error handling related to stored procedures.

I have used the help of article

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

There are two type of errors in SQL Server: fatal and non-fatal.

Fatal errors cause a procedure to abort processing and terminate the connection with the client application.

e.g. The SELECT in the procedure references a table that does not exist, which produces a fatal error.

Non-fatal errors do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error.

The actions that cause a fatal error are not well documented. Each error has an associated severity level that is a value between 0?25. The errors with a severity level of 20 or above are all fatal, but once we get below this value there is no well-defined rule as to which errors are fatal. In truth, though, worrying about which errors are fatal is a bit useless because there is no code we can implement that will allow you us to handle them gracefully.

So how do we implement error handling code for these errors?

@@ERROR
The @@ERROR system function is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client's connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of the function immediately after the target statement executes.

IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'

RAISERROR

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sysmessages table.We can use this statement with the error handling code to implement custom error messages in our applications.

To go into more depth, we can also do error handling using cursors.
Refer:

http://www.sommarskog.se/error-handling-I