You will see how these two work differently. The use and need to raise error is very important for any application. Having said that when we raise an error we want to abort our execution. So we would need to use error severity of 11 or higher only. But this will abort the whole batch. There are times when we will want to abort the execution of a particular SP only rather than the whole batch. Then only under such circumstances we would need to think of using a severity of 10 or lower. You need to understang this behaviour and use the appropriate severity levels in your applications.

WITH option

This is just an added option available with the error messages syntax. The option SETERROR can be used to sets @@ERROR value to msg_id or 50000, regardless of the severity level. The option LOG can be used to log all the error to event log of database server. Use these options if you like to see these messages on the eventviewer window. Check the change in behaviour from of the following code:

RAISERROR ('This is my test message.' , 10, 1 ) WITH SETERROR, LOG Select @@Error

Tale of Return statement

All stored procedures have a return value, determined by the RETURN statement. The RETURN statement takes one optional argument, which should be a numeric value. If you say RETURN without providing a value, the return value is 0 if there is no error during execution. If an error occurs during execution of the procedure, the return value may be 0, or it may be a negative number. The same is true if there is no RETURN statement at all in the procedure: the return value may be a negative number or it may be 0. But what is the this to do in this article is your thought. Well, fundamentally any statement except the Declare syntax will reset the @@Error code status. And Return is no exception. Look at the code below:

Create PROC SP1

AS

BEGIN

            Declare @ID as Int

            SET @ID = 0

            Select @ID = CMN_PersonsID FROM CMN_Persons WHERE CMN_PersonsID = 0

            if @ID = 0

            BEGIN

                        RAISERROR('No record in table T1', 16,1)

                        RETURN(99)

            END

END

GO

CREATE PROC MastSP

AS

BEGIN

            EXEC SP1

            If @@Error <> 0

                        Goto ErrHandler

           

            PRINT 'Success'

            RETURN

 

ErrHandler:

            PRINT 'ERROR'

END

GO

Exec MastSP

If we execute the MastSP, we may expect the ‘ERROR’ to be printed but it will actually print the ‘Success’. This happens because of RETURN(99) statement. As we know that the variable @@ERROR get reset after every statement, in our above example the statement RETURN(99) will reset the @@ERROR to 0. The implementation here should have been as below to get the desired output.

Create PROC MastSP

AS

BEGIN

                Declare @myReturn Int

            EXEC @myReturn=SP1

            If @myReturn = 99

                        Goto ErrHandler

           

            PRINT 'Success'

            RETURN

 

ErrHandler:

            PRINT 'ERROR'

END

GO

Now this code will emmit an error to the caller. My general recommendation here would be to use Return statements properly and also take care of the pitfalls these statements induce. Be very careful of such pitfals and implementations. This go unnoticed easily.

Recommendations

This current version of SQL Server 2000 doesnot have an structured error-handling mechanism. So try to check the @@Error status everytime after everyline. But the general set of guideline would be to use the same after statements that would abort at the statement level. Some of the statement level aborting staements include Duplicate primary key's, NOT NULL Violations, Foreign key constraints, Invalid call to non-existing stored procedures, worong parameter calls to stored procedures, call to objects without permissions, commiting a transaction outside a transaction definition context, redeclaring cursors etc. Some of the errors that abort the batch level include invalid conversion errors (strings to int, strings to GUID's etc), nesting of triggers and stored procedures beyond the 32 level mark, deadlock victims, mismatch in the INSERT-Exec calls etc. 

Just to reinforce the recommendations and the pitafalls we have discussed so far, follwing is a subset of recommendations we need to keep in mind for any production script we write.

1. After each statement, SQL Server sets @@error to 0 if the statement was successful. If the statement results in an error, @@error holds the number of that error. Because @@error is so volatile, you should always save @@error to a local variable before doing anything else with it.
2. In some situations when an error occurs, SQL Server aborts the batch and rolls back any open transaction, but for many errors SQL Server only terminates the statement where the error occurred, and it is your responsibility to roll back any transaction. Since SQL Server is not very consistent in which action it takes, your basic approach to error handling should be that SQL Server might permit execution to continue.
3. Yet an action SQL Server can take in case of an error, is to abandon execution of the current stored procedure, but return control to the calling procedure – without rolling back any transaction, even if it was started by the aborted procedure.
4. The return value from a stored procedure should only serve to indicate whether the stored procedure was successful or not, by returning 0 in case of success, and a non-zero value in case of an error. You are the one who is responsible for that the procedure returns a non-zero value in case of an error.
5. With SET XACT_ABORT ON, you can get SQL Server to abort the batch and rollback the transaction for most errors, but not all errors. Even if you use SET XACT_ABORT ON, you must at a minimum error-check calls to stored procedures.

Conclusion

Error handling is an important part for designing effective applications. The Error Handling system provide database level validations and maintain the integrity of the system in total. So it is very important for us to use the techniques mentioned above. Feel free to ping us anytime on the techniques discussed.

A detailed anaysis on using Error handling has been dealt by fellow MVP Erland. The only comprehensive article ever on this topic I've seen so far. Catch the same at Part I and Part II.