IMPLICIT TRANSACTION and Recursive Stored Procedure

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  September 12, 2022 at 12:28 pm

  #4086590

  I think I'm losing my mind!

  I have a stored procedure which needs to update a number of tables and ensure that the INSERT/UPDATE trigger fires for each record being inserted/amended.   I want super tight control over this so I am setting IMPLICIT TRANSACTIONS

  SET IMPLICIT_TRANSACTIONS ON

  Before the first inster/update

  and

  SET IMPLICIT_TRANSACTIONS OFF

  after the last insert/update

  and each insert/update is wrapped in a specific named transaction

  BEGIN TRANSACTION {Transaction_Name}
  . . . .
  COMMIT TRANSACTION {Transaction_Name}

  HOWEVER....

  The stored procedure is initiated from the SSMS for a 'parent' record and there may also be child records that need to be updated.  These are fetched and then using a cursor the stored procedure calls itself again wrapped in a named transaction.

  I am occassionally getting errors when running the stored procedure saying the transaction count is not zero and I am not quite sure why.   I am thinking it might be because the  IMPLICIT TRANSACTION OFF comes after the recursive call, but I thought the recursive call should happen on a different SPID and so the IMPlICIT status should be independent for each call.  NOTE: for this application I can only have 2 levels.  a Location is either isBlock = 1 and will have one level of children, or it is isBlock = 0 and will have a BlockID

  Cut down code:

  ALTER PROCEDURE Z_Migrate
  (
                  @placeref VARCHAR(100),
                  @managementArea VARCHAR(1000),
                  @username varchar(1000)
  )
  AS
  BEGIN
       SET NOCOUNT ON
       DECLARE @isBlock TINYINT
       /*Get whether proerty is a block*/     
       SET @IsBlock =
       (
            SELECT
                      is_Block
            FROM
                      [Location] AS LOC
            WHERE
                      LOC.ID = @placeref
       )
       /*We want full control over the transaction scoping to ensure that the audit trail records get created*/     
       SET IMPLICIT_TRANSACTIONS ON
       /*Get the block place and update the officer code Management Area, and Offier*/     
        BEGIN TRANSACTION TXN_Update_LOC_mgt_area
             UPDATE
                         [Location]
             SET
                         mgt_area = @MACode,
                         manager = @OfficerCode
             WHERE
                         ID = @placeref
        COMMIT TRANSACTION TXN_Update_LOC_mgt_area
  
        IF @isBlock = 1
        BEGIN
              /*Get the properties under the block*/            
              DECLARE CSR_Dwellings CURSOR  FOR
              SELECT
                             LOC.ID
              FROM
                             [Location] AS LOC
              WHERE
                             LOC.blockID = @placeref
               
              OPEN CSR_Dwellings
              FETCH NEXT FROM CSR_Dwellings INTO @Dwelling
              WHILE @@FETCH_STATUS = 0
              BEGIN
                     /*Recursive Stored Procedure, calls itself for the sub-assets*/                   /*update the patch and offier codes*/                   
                     BEGIN TRANSACTION TXN_SPROC
                           EXEC Z_Migrate
                             @placeref  = @Dwelling,
                             @managementArea = @managementArea,
                             @username  = @username
                    COMMIT TRANSACTION TXN_SPROC
                    FETCH NEXT FROM CSR_Dwellings INTO @Dwelling
               END
               CLOSE CSR_Dwellings
               DEALLOCATE CSR_Dwellings
        END
        SET IMPLICIT_TRANSACTIONS OFF
        SET NOCOUNT OFF
  END

  • This topic was modified 2 years, 8 months ago by  aaron.reese.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  September 13, 2022 at 1:10 pm

  #4087076

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.