SQL Server 2005 introduced a new way of error handling, using try catch blocks similar to what you'll find in Java, C#, or VB.NET.

Here is a template I've been using on the project that I'm currently on to support transactions:

CREATE PROCEDURE dbo.usp_Procedure_Name 
    -- parameter list
AS

SET NOCOUNT ON

BEGIN TRY 
    BEGIN TRANSACTION     

    -- SQL statements

    COMMIT TRANSACTION
END TRY
BEGIN CATCH 
    ROLLBACK TRANSACTION     

    DECLARE @ErrorMessage nvarchar(4000) 
    DECLARE @ErrorSeverity int 
    DECLARE @ErrorState int 
    DECLARE @ErrorNumber int 
    DECLARE @ErrorLine int     

    SET @ErrorMessage = ERROR_MESSAGE() + ' Error Number: %d Error Line: %d' 
    SET @ErrorSeverity = ERROR_SEVERITY() 
    SET @ErrorState = ERROR_STATE() 
    SET @ErrorNumber = ERROR_NUMBER() 
    SET @ErrorLine = ERROR_LINE()     

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)
END CATCH
GO

Basically, a new transaction is started, one or more commands are issued and the transaction is committed if there are no errors. If an error does occur, the transaction is rolled back, and a new error is raised.

The problem I have discovered with this approach is that the RaiseError T-SQL function doesn't support specifying which error should be rethrown, and defaults to 50000. In the following example, the code will be checking for an error code of 547, but will get 50000 instead. This means that the function will return false, even though a foreign key constraint exception occurred.

public static bool IsForeignKeyContraintException(Exception e)
{ 
    SqlException sqlex = e as SqlException;
    if (sqlex != null && sqlex.Number == 547)
    { 
        return true; 
    }

    return false;
}

The only way to report the error is to use the following method instead, which uses GOTO statements.

CREATE PROCEDURE dbo.usp_Procedure_Name 
    -- parameter list
AS

SET NOCOUNT ON

BEGIN TRANSACTION

DECLARE @Error int

-- SQL Statement

SET @Error = @@ERROR
IF (@Error <> 0) 
    GOTO ERROR_HANDLER

-- Next SQL Statement

SET @Error = @@ERROR
IF (@Error <> 0) 
    GOTO ERROR_HANDLER

COMMIT TRANSACTION
GOTO END_RESULT

ERROR_HANDLER: 
    ROLLBACK TRANSACTION

END_RESULT: 
    -- Do nothing
GO

This method reports the correct error number, but isn't nearly as readable. So from what I can tell, the new try…catch functionality isn't very helpful when trying to write conditional code based on the number of the error. I haven't had a chance to check out SQL Server 2008 yet, but hopefully , they decided to add support for rethrowing errors you've encountered.

Has anyone else seen this behavior and found a better way to deal with it?