Sql-server – Reviewing Stored Procedure template for handling transaction

sql serversql-server-2008-r2stored-procedures

I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction.

I have a stored procedure to log error in catch block in a table.

How can I improve my stored procedure to handle transactional error or any kind of error
where stored procedure should not give error on screen.

Can this be improved to handle transaction in proper way or it sufficient?
First Begin tran block or Begin Try block?
Rollback in catch block if @@trancount > 0 sufficient?
Use of Xact_abort on in every Insert, delete, update transaction procedure is ok?

Code:

ALTER PROCEDURE [dbo].[DB_INSERT_MY_LOG]
(@Param1 varchar(30))
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE          @ErrNumber         NVARCHAR(100)   
    ,@ErrDescription    NVARCHAR(4000)  
    ,@ErrorProcedure    NVARCHAR(200)
    ,@ErrState          INT             
    ,@ErrSeverity       INT             
    ,@ErrLine           INT             
    ,@ErrTime           DATETIME

    BEGIN TRAN 
    BEGIN TRY   
                   --Insert or Update or Delete or (Insert & Update together and vice  versa) & multiple insert into different tables.
    COMMIT TRAN
    END TRY

    BEGIN CATCH

    IF (@@TRANCOUNT > 0)  
    ROLLBACK TRAN

    SET @ErrNumber          = ERROR_NUMBER()
    SET @ErrDescription = ERROR_MESSAGE()
    SET @ErrorProcedure    = ERROR_PROCEDURE()
    SET @ErrState           = ERROR_STATE()
    SET @ErrSeverity        = ERROR_SEVERITY()
    SET @ErrLine            = ERROR_LINE()
    SET @ErrTime            = GETDATE()

    EXEC    MyErrorLogInsert    
    @ErrorNumber                = @ErrNumber
    ,@ErrorDescription          = @ErrDescription
    ,@ErrorProcedure            = 'DB_INSERT_MY_LOG'
    ,@ErrorState                = @ErrState
    ,@ErrorSeverity             = @ErrSeverity
    ,@ErrorLine                 = @ErrLine
    ,@ErrorTime                 = @ErrTime

    END CATCH

    SET NOCOUNT OFF;
    SET XACT_ABORT OFF;

Best Answer

It might be worth looking at XACT_STATE instead of @@TRANCOUNT.

From BOL:

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction.

So there may be some cases where, even though there was an error, the transaction should still be committed to the database.

IF (XACT_STATE()) = -1
BEGIN
    PRINT 'The transaction is in an uncommittable state.' +
          ' Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' + 
          ' Committing transaction.'
    COMMIT TRANSACTION;   
END;

Of course, it's entirely up to you whether you want to commit any transaction where an error was raised, even if it is committable... Just because you can, doesn't mean it's always a good idea!

Related Solutions

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

Best Answer

Related Solutions

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

Sql-server – connection pooling, transactions, nested transaction and rollback

Related Question