CODEWITHSUNDEEP
sqlsql-server-2008transactionssql-server-2008-r2
BrooklynSon
BrooklynSon

Reputation: 2103

SQL Server 2008 R2 Transaction is @@error necessary and is ROLLBACK TRANS necessary

My colleague has this in a procedure:

BEGIN TRAN
 --Some deletes and inserts
IF(@@error <> 0)
BEGIN
  ROLLBACK TRAN
  RETURN
END
COMMIT TRAN

I have another in a stored procedure that simply is:

BEGIN TRANSACTION
  --Some deltes and inserts
COMMIT TRANSACTION

I have tested and found that my procedure always rolls everything back during an error (tested for example changing a column data type etc.) without explicitly coding a rollback. Also I have read that using @@error condition is outdated for SQL Server 2005 and above.

What would you say is the correct way of doing a transaction for SQL Server 2008 R2 and above? Thanks

Upvotes: 2

Views: 15902

Answers (3)

Scott
Scott

Reputation: 19

Create the following procedure in your DB then in your catch block, simply exec RethrowError. The nice thing about this is that you dont have to pass any parameters into it from your main stored procedure

CREATE PROCEDURE [dbo].[RethrowError] AS
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
    RETURN;

DECLARE 
    @ErrorMessage    NVARCHAR(4000),
    @ErrorNumber     INT,
    @ErrorSeverity   INT,
    @ErrorState      INT,
    @ErrorLine       INT,
    @ErrorProcedure  NVARCHAR(200);

-- Assign variables to error-handling functions that 
-- capture information for RAISERROR.
SELECT 
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Building the message string that will contain original
-- error information.
SELECT @ErrorMessage = 
    N'Error %d, Level %d, State %d, %s, Line %d' + ERROR_MESSAGE();

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR 
    (
    @ErrorMessage, 
    @ErrorSeverity, 
    1,               
    @ErrorNumber,    -- parameter: original error number.
    @ErrorSeverity,  -- parameter: original error severity.
    @ErrorState,     -- parameter: original error state.
    @ErrorProcedure, -- parameter: original error procedure name.
    @ErrorLine       -- parameter: original error line number.
    );


GO

CREATE PROCEDURE YourProcedure

AS

BEGIN TRANSACTION

BEGIN TRY

--Put your code in here


END TRY


BEGIN CATCH

EXEC RethrowError

END CATCH


END

Upvotes: 0

jean
jean

Reputation: 4350

There a problem with the @@ERROR variable. It's a global variable thus if you are doing something like:

BEGIN TRAN

   --inserts
   --deletes
   --updates
   -- last operation

IF(@@error <> 0)
BEGIN
  ROLLBACK TRAN
  RETURN
END
COMMIT TRAN

@@error contains the result for the last operation only. Thus this piece of code can mask error in previous operations.

My advice is, if you can manage transaction at application level, do it at application level. Handling errors at server side is not for faint hearts and it doesn't improves your application overral robusteness.

Upvotes: 4

marc_s
marc_s

Reputation: 754368

YES, the ROLLBACK is necessary!

I would do a stored procedure based on this template for SQL Server 2005 and newer:

BEGIN TRANSACTION
BEGIN TRY

    -- put your T-SQL commands here    

    -- if successful - COMMIT the work
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- handle the error case (here by displaying the error)
    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

    -- in case of an error, ROLLBACK the transaction    
    ROLLBACK TRANSACTION

    -- if you want to log this error info into an error table - do it here 
    -- *AFTER* the ROLLBACK
END CATCH

Upvotes: 12

Related Questions