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_HANDLERSystem:
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..