Tips and Tricks for:  SQL Server

The Problem:   Does SQL Server have anything like VB's On Error GoTo?

To those familiar with Visual Basic, VB provides the option to use the ON ERROR GOTO ... clause to catch practically any error and handle it in one's program.

Does SQL Server 2000 have the analogous clause that will jump to a certain part of code in a stored procedure to handle errors?

Solution: Errors must be checked after every SQL statement of interest.

In short: No!  SQL Server does not have anything like Visual Basic's "On Error GoTo" construct.  It acts as if there was always an "On Error Resume Next" statement in effect.

For effective error handling in a SQL Server stored procedure you must test the value of @@ERROR after EVERY SQL statement that might produce an error.  Yes, that's a lot of testing.  A well written stored procedure will often be 1/2 or more error handling code.

There's another important consideration in designing error handling: @@ERROR is reset after each and every SQL statement, @@ROWCOUNT is also similarly reset.  Therefore, you must capture both of these immediatly after each SQL statement that might produce an error. 

I start by declaring two variables to hold the values of @@ERROR and @@ROWCOUNT while the error handling code is working with them.  They're declared at the top of every stored procedure and I've added them to my stored procedure template.

DECLARE @myERROR int -- Local @@ERROR
       , @myRowCount int -- Local @@ROWCOUNT

Next let's say that we had a simple INSERT statement

INSERT INTO Authors (au_id, au_fname, au_lname, contract)
     VALUES ('123-45-678'
            , 'Andrew'
            , 'Novick'
            , 1
            )

Immediately after that statement, you should have the following statement:

SELECT @myERROR = @@ERROR
       , @myRowCOUNT = @@ROWCOUNT

These two assignements must be in the same statement.  If you were to split them into two statements, for instance:

SET @myERROR = @@ERROR
SET @myRowCOUNT = @@ROWCOUNT

then @myRowCOUNT would always be zero.  That's because it's reflecting the number of rows effected by the SET @myERROR = @@ERROR statement that preceeds it.

Puling it all together and adding a little error handling you'd get:

USE PUBS
GO

-- These two should always be on when created SPs and UDFs
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE usp_Example_ErrorHandler

/* Example stored procedure to illustrate error handling 
* Example:
DECLARE @RC int
EXEC @RC = usp_Example_ErrorHandler
PRINT 'Return value = ' + CONVERT(varchar(10), @RC)
****************************************************************/ 
AS

DECLARE @myERROR int -- Local @@ERROR
       , @myRowCount int -- Local @@ROWCOUNT

SET NOCOUNT ON

BEGIN TRAN 
    INSERT INTO Authors (au_id, au_fname, au_lname, contract)
         VALUES ('222-22-2222'
                , 'Andrew'
                , 'Novick'
                , 1
                )

    SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR
 
    INSERT INTO  titles(title_id, title, type, price,notes, pubdate)
           VALUES('WW0790'
               , 'Transact-SQL User-Defined Functions'
               , 'popular_comp',  49.95
               , 'Great book.', '2003-11-04')
    SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR

    INSERT INTO titleauthor (au_id, title_id)
           VALUES('222-22-2222', 'WW0790')
    SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR

    COMMIT TRAN -- No Errors, so go ahead

    RETURN 0

HANDLE_ERROR:
    ROLLBACK TRAN
    RETURN @myERROR

GO

Now, let's execute the procedure:

DECLARE @RC int
EXEC @RC = usp_Example_ErrorHandler
PRINT 'Return value = ' + CONVERT(varchar(10), @RC)
(Results)
Return value = 0

To generate an error, run it again:

DECLARE @RC int
EXEC @RC = usp_Example_ErrorHandler
PRINT 'Return value = ' + CONVERT(varchar(10), @RC)
(Results)
Server: Msg 2627, Level 14, State 1, Procedure usp_Example_ErrorHandler, Line 18
Violation of PRIMARY KEY constraint 'UPKCL_auidind'. Cannot insert duplicate key in object 'authors'.
The statement has been terminated.
Return value = 2627
 

Notice that the error message has been returned, even though the code proceeded to capture and return the error value.  This allows client code, such as ADO.Net and ADO to capture the message and return it to their caller.

There are other styles of error handling.  Your choice depends on how you want your stored procedure to interact with it's callers.  In this case I've used the proc's return value but still returned the error.  That means that the client must check the return after every call to this procedure.  Looks like there might be enough material to turn this into an article.