writing a transaction in t-sql and error handling

Question

Do u think there is a better way to write a transaction in t-sql? Is there a better approach that improves maintainability and performance of the application that uses this transaction?

-- Description: Insert email Receiver under specified subject
-- =============================================
ALTER PROCEDURE [Contact].[Receiver_stpInsert]
    @First_Name nvarchar(30),
    @Last_Name nvarchar(30),
    @Email varchar(60),
    @Subject_Id int
AS
BEGIN   
    SET NOCOUNT ON;

    DECLARE @error_num int;


    BEGIN TRANSACTION 

    INSERT INTO Contact.Receiver(First_Name, Last_Name, Email) VALUES(@First_Name, @Last_Name, @Email); 

    SET @error_num = @@ERROR;
    IF (@error_num <> 0)
        BEGIN
            ROLLBACK;
            RETURN;
        END

    DECLARE @rec_record_id int;
    SET @rec_record_id = (SELECT Record_Id FROM Contact.Receiver WHERE Email = @Email);

    SET @error_num = @@ERROR;
    IF (@error_num <> 0)
        BEGIN
            ROLLBACK;
            RETURN;
        END

    INSERT INTO Contact.Receiver_Subject(Receiver_Id, Subject_Id) VALUES(@rec_record_id, @Subject_Id);

    SET @error_num = @@ERROR;
    IF (@error_num <> 0)
        BEGIN
            ROLLBACK;
            RETURN;
        END

    SET @error_num = @@ERROR;
    IF (@error_num <> 0)
        BEGIN
            ROLLBACK;
            RETURN;
        END
    ELSE
        BEGIN   
            Commit;

        END

END

Answer 1

If you're using SQL 2005 or later, you can use the TRY...CATCH block, like this:

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Contact.Receiver(First_Name, Last_Name, Email) VALUES (@First_Name, @Last_Name, @Email); 
    ... other inserts etc 
    ...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

This way, you don't keep repeating the same blocks of code checking @@ERROR. If you want to know what error occurred, in the BEGIN CATCH block you can get various bits of info:

• 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.

Answer 2

For a long time now I've been advocating the use of TRY/CATCH and nested transactions in stored procedures.

This pattern gives you not only the much simplified error handling of the TRY/CATCH block compared with the @@ERROR check, but it also gives all-or-nothing nested semantics for procedure invocations.

If the procedure is called on the context of a transaction then the procedure rolls back only its own changes and leaves the caller to decide whether to rollback the embedding transaction or to try an alternate error path.

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
end

The draw backs of this approach are:

• It does not work with distributed transactions. Because transaction savepoints are incompatible with distributed transactions, you cannot use this pattern when distributed transactions are required. IMHO distributed transactions are evil and should never be used anyway.
• It alters the original error. This problem is inherent in TRY/CATCH blocks and there is nothing you can do about it. An application that is prepared to deal with the original SQL Server error codes (like 1202, 1205, 2627 etc) will have to be changed to deal with the error codes in the above 50000 range raised by Transact-SQL code that uses TRY/CATCH.

Also a word of caution about the use of SET XACT_ABORT ON. This setting will cause a batch to abort a transaction at any error. That raises any TRY/CATCH transaction handling basically useless and I recommend to be avoided.

Answer 3

If you have SQL Server 2000 or before, then yes - checking the @@ERROR value is basically all you can do.

With SQL Server 2005, Microsoft introduced the TRY...CATCH construct which makes it a lot easier:

BEGIN TRY
  ......
  -- your T-SQL code here
  ......
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

    -- do other steps, if you want
END CATCH