Stored procedures with exception handling in SQL Server

Introducing TRY...CATCH

Structured exception handing provides a powerful mechanism for controlling complex programs that have many dynamic runtime characteristics. It is a tried and true practice currently supported by many popular programming languages such as Microsoft Visual Basic .Net and Microsoft Visual C#. You will see in the examples below that utilizing this robust method will make your code more readable and maintainable. The TRY block contains transactional code that could potentially fail, while the CATCH block contains code that executes if an error occurs in the TRY block. If any errors occur in the TRY block, execution is diverted to the CATCH block and the error can be handled while error functions can be used to provide the detailed error information. TRY…CATCH has the following abbreviated syntax:

BEGIN TRY RAISERROR ('Houston, we have a problem', 16,1) END TRY BEGIN CATCH SELECT ERROR_NUMBER() as ERROR_NUMBER, ERROR_SEVERITY() as ERROR_SEVERITY, ERROR_STATE() as ERROR_STATE, ERROR_MESSAGE() as ERROR_MESSAGE END CATCH 

Notice the use of functions in the script above that we are able to use in place of local and/or global variables. These functions should only be used in a CATCH BLOCK and are explained below:

• ERROR_NUMBER() returns the number of the error.
• ERROR_SEVERITY() returns the severity.
• ERROR_STATE() returns the error state number.
• ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
• ERROR_LINE() returns the line number inside the routine that caused the error.
• ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names or times.

----------------------------------------------

----------------------------------------------

create proc P_Insert_New_BookTitle_2K5

(@TitleName nvarchar(128),

 @Price money,

 @au_fname nvarchar(32),

 @au_name nvarchar(64),

 @CommissionRating int)

as

declare @err int,

@tablename sysname,

@errormessage nvarchar(2000)

BEGIN TRY

begin transaction

select @errormessage = 'insert into Titles table failed',

@tablename = 'Titles'

insert dbo.Titles (TitleName, Price)

values (@TitleName, @Price)

select @errormessage = 'insert into Authors table failed',

@tablename = 'Authors'

insert dbo.Authors  (au_fname, au_lname, TitleID, 

                     CommissionRating)

values (@au_fname, @au_fname, @@IDENTITY, 

        @CommissionRating)

commit transaction

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

-- Log the error 

insert dbo.Application_Error_Log (UserName, tableName,     

            errorNumber, errorSeverity, errorState, errorMessage)

values (suser_sname(), @tableName, ERROR_NUMBER(),  

            ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())  

RAISERROR (@errormessage, 16,1)

END CATCH

----------------------------------------------

exec P_Insert_New_BookTitle_2K5 'Red Storm Rising',16.99, 'Tom','Clancy', 200 

When we execute the stored procedure with the provided parameters, the insert into the Authors table fails because of an invalid Commission Rating value. When this happens, execution is diverted to the CATCH block, which rolls back our transaction and inserts a row into our Application_Error_Log using the SQL Server 2005 supplied functions.