Is this right approach to implement nested Transaction

Question

Post reply

Is this right approach to implement nested Transaction

gaurav

SSC-Addicted

Points: 404

April 27, 2022 at 10:09 am

#4022935

Hello Everyone,

I am using Microsoft SQL Azure (RTM) - 12.0.2000.8.

I have a store procedure as below.

ALTER PROC [dbo].[usp_SAP_to_IC]  -- exec [dbo].[usp_transfer_on_premise_project_data_to_cp_database_IC_12] 

AS
BEGIN TRY
BEGIN TRANSACTION;
   
     EXEC [dbo].[usp_transfer_on_premise_project_data_to_cp_database_IC_12]
     EXEC [dbo].[usp_transfer_on_premise_project_data_to_cp_database_IC_10]
     EXEC [dbo].[usp_transfer_on_premise_project_data_to_cp_database_IC_7]

        COMMIT TRANSACTION;
  END TRY
      BEGIN CATCH
INSERT INTO ExceptionLog ( ErrorLine, ErrorMessage, ErrorNumber, ErrorProcedure, ErrorSeverity, ErrorState, DateErrorRaised) VALUES (Error_Line(), Error_Message(), Error_Number(), Error_Procedure(), Error_Severity(), Error_State(), GETDATE())
        -- Transaction uncommittable
    IF (XACT_STATE()) = -1
      ROLLBACK TRANSACTION
-- Transaction committable
    IF (XACT_STATE()) = 1
      COMMIT TRANSACTION
  END CATCH ;

Here I have used TRY CATCH with transactions.

Again I am using TRY CATCH with transaction with every procedure executing in [dbo].[usp_SAP_to_IC] procedure.

Lets take example of 1st store procedure. Let me show you what [dbo].[usp_transfer_on_premise_project_data_to_cp_database_IC_12] procedure has in it.

ALTER PROC [dbo].[usp_transfer_on_premise_project_data_to_cp_database_IC_12]

AS
BEGIN TRY
BEGIN TRANSACTION;

--Combine ProjectNo7, ProjectNo3, ProjectNo2 in ProjectNo
UPDATE ProjectRaw SET ProjectNo = SUBSTRING(ProjectNo7, 1, 2)+'.'+SUBSTRING(ProjectNo7, 3, 4)+'.'+ SUBSTRING(ProjectNo7, 7, 1) +'-'+ ProjectNo3 +'.'+ProjectNo2

        MERGE IC_PN_Number_1 AS IC -- final table (AdminMater_GEN)
        USING ProjectRaw AS PR -- temp table

         ON IC.Pno  = PR.ProjectNo 
            WHEN NOT MATCHED BY TARGET--0 row effected
            THEN
              INSERT( Pno, Pname,  PAV,  status, dateadded,addedby,   CountryID
  )
              VALUES
        (PR.ProjectNo,
         PR.ProjectName,
         (SELECT vcEmail FROM HRSAP_DATA WHERE SAPno=PR.AVSAP),
  1,GETDATE(),1,
  (SELECT bintid_pk FROM Country_Master WHERE country_code=PR.CountryCode ))
      
     

        --START : UPDATE EXISTING RECORDS--
            WHEN MATCHED
            THEN UPDATE SET 
                            IC.status = 1, 
                            IC.Pno = PR.ProjectNo,
                            IC.Pname = PR.ProjectName,
                            IC.PAV =  (SELECT vcEmail FROM HRSAP_DATA WHERE SAPno=PR.AVSAP),
                            IC.datemodi = GETDATE(),
IC.updatedby =1,
                            IC.countryID =(SELECT bintid_pk FROM Country_Master WHERE country_code=PR.CountryCode )

        --END : UPDATE EXISTING RECORDS--
            WHEN NOT MATCHED BY SOURCE
        --IN CASE JSON DOES NOT CONTAINS THE RECORD THEN DEACTIVATE THE PROJECT
            THEN UPDATE SET 
                            IC.Status = 0;

        COMMIT TRANSACTION;
  END TRY
      BEGIN CATCH
                --SET @pintCheck = 2  
             
INSERT INTO ExceptionLog ( ErrorLine, ErrorMessage, ErrorNumber, ErrorProcedure, ErrorSeverity, ErrorState, DateErrorRaised) VALUES (Error_Line(), Error_Message(), Error_Number(), Error_Procedure(), Error_Severity(), Error_State(), GETDATE())
        -- Transaction uncommittable
    IF (XACT_STATE()) = -1
      ROLLBACK TRANSACTION
-- Transaction committable
    IF (XACT_STATE()) = 1
      COMMIT TRANSACTION
  END CATCH ;

So my question is : it is okay to use like I am using? OR should I change my approach?

Request you kindly suggest.

Thank you.

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

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

Michael L John One Orange Chip Points: 27042 More actions · Answer 1

This is nearly identical to the example provided by Microsoft.

I question testing the transaction state in the catch block. If there is an error thrown, regardless of the state of the transaction, would you ever want to commit the transaction?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

gaurav SSC-Addicted Points: 404 More actions · Answer 2

Michael L John wrote:

This is nearly identical to the example provided by Microsoft.
I question testing the transaction state in the catch block. If there is an error thrown, regardless of the state of the transaction, would you ever want to commit the transaction?

No