sql server: inserting into a table within a transaction for logging error occurance?

Question

I have a SP (Stored procedure) which is contained of some T-SQL statements.....

All of T-sql statements are in a transaction block and by occuring any error, I rollback every thing.

like this:

BEGIN TRANSACTION
.....
.....
IF @X=1
BEGIN
    declare cu cursor for select col1,col2 from Table1 where Id=@Y
    open  cu
    fetch next from cuinto @A, @B
    while  @@Fetch_Status = 0
    BEGIN
         .....
        ......
        IF @@ERROR <>0
        BEGIN
            ROLLBACK TRANSACTION
            RETURN
        END
END
.....
.....

The Sp does not run properly and I can't find what the resean of it is..... I think it's a good idea to log every operation within sp by inserting some data into a table My Question is:

Because it uses a transaction, every insertion will be rolled back.....

What's your opinion? Is there any other way?

Thank you

possible duplicate of [Is this good writen transaction in stored procedure](http://stackoverflow.com/questions/6321554/is-this-good-writen-transaction-in-stored-procedure) — gbn, Jun 13 '11 at 09:03

score 5 · Answer 1 · answered Jun 13 '11 at 08:20

3 things:
1) please, please don't use cursors if you don't have to.
2) you can log by using either RAISERROR WITH LOG or by inserting data into a table variable and then inserting that into a real table after you've rolledback your transaction. This is possible because table variables are transaction independent.
3) Use the try catch block

score 3 · Answer 2 · edited May 23 '17 at 11:51

3

There is no reason to use @@ERROR now: TRY/CATCH is far more reliable. To understand more then I recommend reading Erland Sommarskog's "Error Handling in SQL 2005 and Later" which is one the definitive articles on the subject

In this case, without TRY/CATCH, some errors are batch aborting: this means the code stops and no error is trapped. This is fixed with TRY/CATCH except for compile errors.

This template is taken from my previous answer Nested stored procedures containing TRY CATCH ROLLBACK pattern?

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
    -- if desired INSERT ExceptionLogTable () .. 
END CATCH
GO

If you use SET XACT_ABORT ON (which I reckon should be best practice), then in any CATCH block @@trancount is zero. So you can write into a logging table here if you wish, in addition to throwing an error.

edited May 23 '17 at 11:51

Community

1
1

answered Jun 13 '11 at 08:59

gbn

422,506
82
585
676

+1 for SET XACT_ABORT is ON studied. Got to learn new. Thanks :) – Pankaj Jun 13 '11 at 09:12
@SQL: ..combined with TRY/CATCH makes your code far more reliable, We use it all the time. http://stackoverflow.com/search?q=user%3A27535+XACT_ABORT – gbn Jun 13 '11 at 09:14
Yes. Understood. I have it in my practice as well. But my @@error , this should not be present really. – Pankaj Jun 13 '11 at 09:23
@gbn: When @starttrancount = 0 how do you commit transaction? I mean there's no transaction. Right? – odiseh Jun 13 '11 at 10:05
@odiseh: this pattern does not begin/commit/rollback if there is already a transaction (@@TRANCOUNT > 1). That is, it supports nested stored procedures. When @@TRANCOUNT = 0, then I do begin/commit/rollback in the code. @starttrancount is used to keep the state of @@TRANCOUNT throughout because any BEGIN will change it of course. – gbn Jun 13 '11 at 10:07
@gbn: so you mean " IF @starttrancount > 0 COMMIT TRANSACTION " is NOT correct? – odiseh Jun 13 '11 at 10:19
@odiseh: Yes. You can only use @starttrancount because this is the state of @@TRANCOUNT at the beginning of the stored proc. – gbn Jun 13 '11 at 10:38

score 1 · Answer 3 · answered Jun 13 '11 at 08:14

I rewrote your code to give you a real example by using the Transaction and Try Catch

CREATE PROCEDURE [dbo].[mySP] 
(
    @X int, @Y int,
    @Return_Message VARCHAR(1024) = ''  OUT
)
AS

    SET NOCOUNT ON;

    Declare @A varchar(100) @B varchar(100)

BEGIN TRY

    BEGIN TRAN

        IF @X=1
        BEGIN
            declare cu cursor for select col1,col2 from Table1 where Id=@Y
            open  cu
            fetch next from cu into @A, @B
            while  @@Fetch_Status = 0
            BEGIN
                -- .....
                -- do your stuff
                FETCH NEXT FROM cu into @A, @B              
            END
        END

    COMMIT TRAN

    SELECT  @Return_Message = 'All OK'

    /*************************************
    *  Return from the Stored Procedure
    *************************************/
    RETURN 1   -- success

END TRY

BEGIN CATCH
    /*************************************
    *  if errors rollback
    *************************************/
    IF @@TRANCOUNT > 0 ROLLBACK

    SELECT @Return_Message = @ErrorStep + ' '
        + cast(ERROR_NUMBER() as varchar(20)) + ' line: '
        + cast(ERROR_LINE() as varchar(20)) + ' ' 
        + ERROR_MESSAGE() + ' > ' 
        + ERROR_PROCEDURE()

    /*************************************
    *  Return from the Stored Procedure
    *************************************/
    RETURN 0   -- fail

END CATCH

SP Usage:

declare @ret int, @Return_Message VARCHAR(1024)

EXEC @ret = mySP 1, 2, @Return_Message OUTPUT

-- the SP Fail so print or store the return message with errors ...
if @ret = 0 print @Return_Message

Pankaj · Answer 4 · 2011-06-13T09:09:37.917

You can also implement Exception Handling using Try Catch as well

Begin Try
    BEGIN TRANSACTION
.....
.....
IF @X=1
BEGIN
    declare cu cursor for select col1,col2 from Table1 where Id=@Y
    open  cu
    fetch next from cuinto @A, @B
    while  @@Fetch_Status = 0
    BEGIN
        .....
        ......
        //Your insert statement....
END
.....
.....
Commit Tran

End Try

Begin Catch
      Rollback Tran
      DECLARE @ErrorMessage NVARCHAR(4000);
      DECLARE @ErrorSeverity INT;
      DECLARE @ErrorState INT;

      SELECT @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();

      -- Use RAISERROR inside the CATCH block to return 
      -- error information about the original error that 
      -- caused execution to jump to the CATCH block.
      RAISERROR (@ErrorMessage, -- Message text.
           @ErrorSeverity, -- Severity.
           @ErrorState -- State.
           );

End Catch

@@ERROR isn't reliable enough: always use TRY/CATCH – gbn Jun 13 '11 at 09:03 — gbn, Jun 13 '11 at 09:03

sql server: inserting into a table within a transaction for logging error occurance?

4 Answers4