Better Error Handling in SQL Server 2005 with TRY..CATCH

(Try #2 ...)
I'm pretty excited to see that there is some real error handling for T-SQL code in SQL Server 2005. It's pretty painful to have your wonderfully architected .NET solution tainted by less-than-VBScript error handling for stored procedures in the database. The big difference being  the addition of TRY..CATCH blocks. Let's take a look:

First, we have the client access code. Pretty straightforward. It will check for validation exceptions (which we'll define as Error 16, State 2), and handle those in a graceful manner. If it's unhandled it will choke..

.NET Code

Dim cmd As New SqlCommand() 
cmd.CommandText = "Approve_Proposal"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = New SqlConnection(Configuration.ConnectionString)
Try
cmd.Connection.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
Dim vEx As New DataValidationException()
For i As Integer = 0 To ex.Errors.Count -1
If ex.Errors(i).State = 2 Then 'it is a validation problem
vEx.Messages.Add(ex.Errors(i).Message)
Else 'it's a bigger problem than validation
Throw ex
End If
Next i
Throw vEx 'Since we are here, there were only validation exceptions
Finally
cmd.Connection.Close()
End Try

And now the stored proc code, in T-SQL for SQL Server 2000. Note the technique we have to use ... checking only for an error number, using GOTO, etc ...

Stored Procedure - 2000

CREATE PROCEDURE Approve_Proposal(
@ProposalNum CHAR(8)
,@EmployeeNum CHAR(5) ) AS
BEGIN
BEGIN TRANSACTION



/* since @@ERROR will only return the error from
the last statement, to use unified error handling
within a procedure, we have to use this every time */

DECLARE @ErrorNum INT

/* If we don't record where in the procedure we are when
the error is thrown, we'll have no idea how to fix it! */

DECLARE @ErrorStep VARCHAR(25)


/* validate input - ProposalNum
Note how we have to raise an error, and then GOTO the code */

IF NOT EXISTS(SELECT 1 FROM Proposals WHERE Status='Pending' AND Number=@ProposalNum) BEGIN
RAISERROR
('%s does not represent a proposal pending approval.', 16, 2,@ProposalNum)
GOTO ERROR_HANDLER
END

/* validate input - EmployeeNum */
IF NOT EXISTS( SELECT 1 FROM Employees E
INNER JOIN Employee_Roles R ON E.Number = R.Employee_Number
WHERE E.Number=@EmployeeNum AND R.Role_ID='Approver') BEGIN
RAISERROR
('%s is not authorized to approve proposals.', 16, 2, @EmployeeNum)
GOTO ERROR_HANDLER
END

/* Insert the approval
Note how we have to set, then try the errornumber */

SET @ErrorStep='Insert Approval'
INSERT INTO Proposal_Approvals (Proposal_Number,Employee_Number,[Date])
VALUES (@ProposalNum,@EmployeeNum,GETDATE()) SET @ErrorNum = @@ERROR
IF @ErrorNum<>0 GOTO ERROR_HANDLER

/* You see the pattern for user/system errors?
User:
RAISERROR (...)
GOTO ERROR_HANDLER
 System:
SET @ErrorStep = 'Something'
{Some Statement}
SET @ErrorNum = @@ERROR
IF @ErrorNum<>0 GOTO ERROR_HANDLER
*/


COMMIT TRANSACTION
RETURN


/* note the user of a GOTO/LABEL ... ewwww */
ERROR_HANDLER:
/* Rollback if the transaction is still around */
IF @@TRANCOUNT>0 ROLLBACK


/* The only information we have about the error at this point is the error number.
Because of this, we have to have a central stored proc to
handle and log other errors. And then we can only guess Log it
only if it's not a user defined(50000) */

IF @ErrorNum<>50000 EXEC LOG_ERROR('Approve_Proposal',@ErrorNum,@ErrorStep)

/* Note, error is always returned to the client */

END

And now for the updated version ... much, much cleaner!

Stored Procedure - 2005

CREATE PROCEDURE Approve_Proposal(
@ProposalNum CHAR(8)
,@EmployeeNum CHAR(5) ) AS BEGIN

/* You betcha! We got TRY/CATCH blocks now! */
BEGIN TRY


BEGIN TRANSACTION


/* Woo, No ErrorNum/Step! */

/* validate input - ProposalNum - no GOTO Needed! */

IF NOT EXISTS(SELECT 1 FROM Proposals WHERE Status='Pending' AND Number=@ProposalNum)
RAISERROR ('%s does not represent a proposal pending approval.', 16, 2,@ProposalNum)

/* validate input - EmployeeNum */

IF NOT EXISTS( SELECT 1 FROM Employees E
INNER JOIN Employee_Roles R ON E.Number = R.Employee_Number
WHERE E.Number=@EmployeeNum AND R.Role_ID='Approver')
RAISERROR ('%s is not authorized to approve proposals.', 16, 2, @EmployeeNum)

/* Insert the approval -- hey look, just a simple statement!*/
INSERT INTO Proposal_Approvals (Proposal_Number,Employee_Number,[Date])
VALUES (@ProposalNum,@EmployeeNum,GETDATE())

/* And So on, and So on */

COMMIT TRANSACTION
END TRY

BEGIN CATCH
/* We have a better way to handle the rollback as well.
From the docs:
XACT_STATE = 0 means there is no transaction
XACT_STATE = -1 means it is uncomittable
XACT_STATE = 1 means it's valid, which it wouldn't be at
this point because of the error thrown */

IF (XACT_STATE())=-1 ROLLBACK TRANSACTION

/* And how about that, we actually have more info about the error! */

/* If it's a validation error (which we use 16/2 for) ... */

IF ERROR_NUMBER()=16 AND ERROR_STATE()=2 BEGIN
RAISERROR
(ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_STATE())
END

/* There could be a whole host of other somewhat expected errors we can trap, it's fairly simple.
would work just like this:
ELSE IF ERROR_NUMBER()=?? AND ERROR_STATE()=?? BEGIN
{Handle the error, swallow it, whatever you need }
END */

/* Otherwise, it's more severe, log it, and send a generic message. */

ELSE BEGIN
EXEC
LOG_ERROR('Approve_Proposal',ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_STATE())
RAISERROR('An unknown error has occured.',16,1)
END
END CATCH


END

Note: there may be syntax errors in the code. I didn't actually test it ... but you get the idea..

Published Wednesday, February 2, 2005 2:45 PM by Alex Papadimoulis

Comments

Thursday, February 24, 2005 6:07 AM by Chris

# re: Better Error Handling in SQL Server 2005 with TRY..CATCH

Can you give examples of errors that cause XACT_STATE()=-1? I've tried tran abort errors such as conversion errors, but they still don't do it.

Also can you elaborate on the meaning of the ERROR_STATE() function?

Thanks in anticipation
Tuesday, January 30, 2007 5:21 AM by How is the error handling in stored proc of T-SQL?

# re: Better Error Handling in SQL Server 2005 with TRY..CATCH

How is the error handling in stored proc of T-SQL?

Thursday, October 4, 2007 5:20 AM by Ketan Thacker

# re: Better Error Handling in SQL Server 2005 with TRY..CATCH

When a transaction inside the Try block fails the value of XACT_STATE() becomes -1. i.e. the transaction becomes uncommitable.

To check this, do the following:

Create table dbo.test1(id int, name1 varchar(10))

BEGIN TRY

   BEGIN TRAN

   insert into dbo.test1(id,name1) values ('z','zzz')

   COMMIT TRAN

END TRY

BEGIN CATCH

   SELECT XACT_ERROR()

   IF XACT_ERROR() = -1

       ROLLBACK TRAN

END CATCH

The above insert will fail since the column "id" in the table expects it to be an int value. The XACT_STATE() value will be -1

Thursday, January 17, 2008 7:18 PM by iamdacian

# re: Better Error Handling in SQL Server 2005 with TRY..CATCH

your sample is not complete. in the catch block if the XACT_ERROR() is 1 than you have to rollback your changes because you own the transaction; in reality is more complicated than this because what if your caller already opened a transaction? are you going to rollback your caller's tran? think about it.

and then more, the try catch is not catching all the error, the fatal error are not caught. which means if a fatal error happens your transaction is doomed. you have to take care of it in the caller.

and there's more, but i'll let you  figure it out :)

Friday, August 22, 2008 3:36 PM by Ryan W

# re: Better Error Handling in SQL Server 2005 with TRY..CATCH

I'm thinking of creating an error handler on two dates from two different table...any ideas on starting this statement?

Thanks