IMPLICIT TRANSACTION and Recursive Stored Procedure

Question

Post reply

IMPLICIT TRANSACTION and Recursive Stored Procedure

aaron.reese

SSChampion

Points: 13498

September 12, 2022 at 12:28 pm

#4086589

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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