Error handling in Stored Procedure

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s