Need Clarification on Transaction SQL SERVER

Question

I read this Stackoverflow question Nested stored procedures containing TRY CATCH ROLLBACK pattern?

I'm in need of clarification on Transaction template what gbn have answered. I couldn't comment and ask there.

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]
END CATCH
GO

My Question is!

Why to use ?

SELECT @starttrancount = @@TRANCOUNT , rather than using @@TRANCOUNT directly??

and why to check this?

IF @starttrancount = 0 BEGIN TRANSACTION

IF @starttrancount = 0 COMMIT TRANSACTION

I'm new to transaction , explanation with example would be so helpfull. Thanks :)

SundaraPandian · Accepted Answer · 2013-09-28T08:40:53.317

IF @starttrancount = 0 BEGIN TRANSACTION

IF @starttrancount = 0 COMMIT TRANSACTION

These are used because, the @starttrancount guaranteed to let only the outer most stored procedure to use transaction, so that only one transaction exists.

Example: We want to execute outer most procedure, then the transaction must be used in outer most procedure only.

Outer Stored Procedure

CREATE PROCEDURE sp_outer
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT -- Initially @@TRANSCOUNT =0

    IF @starttrancount = 0
        BEGIN TRANSACTION     -- @@TRANSCOUNT =1

       EXEC sp_inner  -- Inner Procedure is called with @@TRANSCOUNT =1 
                      -- so that Transaction in inner procedure will not be used. 
                      -- Per Transaction is exists.

    IF @starttrancount = 0 
        COMMIT TRANSACTION    -- @@TRANSCOUNT = 0
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION  -- If Error occurs Rollback takes place.
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

2.Inner Stored Procedure

CREATE PROCEDURE sp_inner
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT  -- @@TRANCOUNT =1

    IF @starttrancount = 0 
        BEGIN TRANSACTION   -- Skipped

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

    IF @starttrancount = 0 
        COMMIT TRANSACTION  -- Skipped
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION -- if Error Caught Roll back does not happen here
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc] -- Error thrown to outer stored procedure.
END CATCH
GO

why SELECT @starttrancount = @@TRANCOUNT , rather than using @@TRANCOUNT directly??

Since @@TRANSCOUNT scope exists in both the stored procedures, for maintaining values within the procedure's scope @starttrancount variable is used.

Need Clarification on Transaction SQL SERVER

1 Answers1