"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" - Error encountered in SQL Server

During development of stored procedures, I ran into one issue and this was due to not applying best practice of stored procedures, practice and this issue come mostly when we call stored procedures inside another one and so on, which are used in nested in transactions

So I would like to write the next post for the stored procedures development, as how we should write it, It will be a just a template of best practice. In this post I will explain the details of the issue which I suffered and how can we resolve it. Let's for the demonstration we will create one table and stored procedures.

-- Creating table which will be used in SPs.
CREATE TABLE tbl_Tran
  (
     TranId    INT NOT NULL PRIMARY KEY
     ,TranName VARCHAR(10)
  )

GO


-- Creating first stored procedure.
CREATE PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION

    -- Inserting records in table,
    -- Due to this insert error will be raised.
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION
      END TRY

      BEGIN CATCH
          ROLLBACK TRANSACTION

          PRINT 'Rollback Tran1'
      END CATCH
  END

GO

-- Creating Second stored procedure
CREATE PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

    -- Calling first created stored procedure here.
          EXEC Firttranproc

          COMMIT TRANSACTION
      END TRY

      BEGIN CATCH
          ROLLBACK TRANSACTION

          PRINT 'Rollback Tran2'
      END CATCH
  END

GO

-- Executing second stored procedure here and see what happen.
EXEC Secondtranproc

GO

In first stored we have written a code to raise an error and due to transaction issue while running second stored procedure it raised the error. This is not best practice as we have written only "Begin Transaction". We should specify the name of the stored procedure.

# 1 Solution : Let's specify the name of transactions in both of the stored procedures and altering stored procedures here and execute it again with same.

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON
   
   -- Here we have specified Tran1 as transaction name
 
   BEGIN TRY
          BEGIN TRANSACTION Tran1

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
   
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION Tran1
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran1'

          ROLLBACK TRANSACTION Tran1
      END CATCH
  END

GO

-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

  -- Here we have specified Tran2 as transaction name

      BEGIN TRY
          BEGIN TRANSACTION Tran2

    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

          -- Calling first stored procedure here
          EXEC Firttranproc

          COMMIT TRANSACTION Tran2
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran2'

          ROLLBACK TRANSACTION Tran2
      END CATCH
  END

GO


-- Executing second stored procedure which will also call of SP1 
EXEC Secondtranproc

GO

You can see here the error is not raised which is raided in the first case. Here stored procedure find and commit/rollback correct and their transactions only.

# 2 Solution : Let's look another alternative method which can also help to come out from the issue.

The rewritten stored procedures,

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON
   
   -- Here we have specified Tran1 as transaction name
 
   BEGIN TRY
          BEGIN TRANSACTION 

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
   
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION 
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran1'

   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION 
      END CATCH
  END

GO

-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION 

    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

          -- Calling first stored procedure here
          EXEC Firttranproc

          COMMIT TRANSACTION 
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran2'

    
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION 
      END CATCH
  END

GO


-- Executing second stored procedure which will also call of SP1 
EXEC Secondtranproc

GO

Here is the same output as second case, it is also not raised the error.

Let me know what you think over here. You also encountered this issue in the past, Please share your experience and alternation solution.