SQl Server error handling pattern

Question

Hi all. I am not an expert on SQl Server. Is this a valid pattern for handling errors in a batch of SELECT, INSERT...in SQl SERVER ? (I use v.2008)

BEGIN TRANSACTION
    BEGIN TRY
       -- statement 1
       -- statement 2
       -- statement 3
       COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
       ROLLBACK TRANSACTION
    END CATCH

Thanks

Answer 1

Nearly:

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

This was taken from the MSDN documentation on TRY/CATCH where other examples can be found: http://msdn.microsoft.com/en-us/library/ms175976.aspx

Answer 2

I use something like this:

CREATE PROCEDURE  ErrorHandlingPattern
(     @intParam      int
     ,@varcharParam  varchar(10)
     ,@dateParam     datetime
) 
AS

BEGIN TRY
    SET NOCOUNT ON
    DECLARE @Rows              int           --store @@ROWCOUNT in this
           ,@ErrorMsg          varchar(500)  --temp string to build the contents of messages passed into RAISERROR calls
           ,@LogInfo           varchar(5000) --will hold any info necessary for error debugging, append to this throughout the procedure with important info
           ,@TransactionCount  int           

    SELECT @TransactionCount=@@TRANCOUNT
          ,@LogInfo='@intParam='     +ISNULL(''''+CONVERT(varchar(10),  @intParam       )+'''','NULL')
                 +', @varcharParam=' +ISNULL(''''+                      @varcharParam    +'''','NULL')
                 +', @dateParam='    +ISNULL(''''+CONVERT(varchar(10),  @dateParam,121  )+'''','NULL')
                 +'; @@TRANCOUNT='   +ISNULL(''''+CONVERT(varchar(10),  @@TRANCOUNT     )+'''','NULL')

    --validate parameters
    IF @intParam ....
    BEGIN --logical error
        SET @ErrorMsg='Error, invalid value for @intParam: '+ISNULL(''''+CONVERT(varchar(10),@intParam)+'''','NULL')
        RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
    END

    IF @TransactionCount=0  --if we are already in a transaction, no need to start another, nesting transactions +rollback=warnings about transaction count not being the same as when the procedure started.
    BEGIN
        BEGIN TRANSACTION
    END

    --do your work here....
    INSERT/UPDATE/DELETE...
    SELECT @Rows=@@ROWCOUNT

    IF @Rows!=ExpectedValue
    BEGIN --logical error
        SET @ErrorMsg='Error, INSERT/UPDATE/DELETE of tableXYZ resulted in '+ISNULL(''''+CONVERT(varchar(10),@Rows)+'''','NULL')+' rows affected'
        RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
    END

    --append improtant info to log string
    SET @LogInfo=ISNULL(@LogInfo,'')+'; INSERT/UPDATE/DELETE of tableXYZ resulted in '+ISNULL(''''+CONVERT(varchar(10),@Rows)+'''','NULL')+' rows affected'

    IF @TransactionCount=0 --only end the transaction if it started here
    BEGIN
        COMMIT --put in try block to be able to catch any problems committing
    END
END TRY
BEGIN CATCH

    IF XACT_STATE()!=0 --if there is any error end the transaction ASAP
    BEGIN
        ROLLBACK TRANSACTION
    END

    --will echo back the complete original error message
    DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
    SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)

    --because the transaction was ROLLBACKed this insert will be recorded in the database
    INSERT INTO YourErrorLog (...) VALUES (...ISNULL(@ErrorMessage,'')+ISNULL(@LogInfo,''))

    RETURN 999

END CATCH

RETURN 0
GO

Since you are just doing a batch of a batch of SELECT, INSERT, you can just remove the CREATE PROCEDURE and parameter declarations and have the first line start at BEGIN TRY. Also, because you are not creating a procedure, replace any RETURN statements with GOTO TheEnd and add a TheEnd: label at the script's bottom.