Link to home
Start Free TrialLog in
Avatar of Meinhoonaa
Meinhoonaa

asked on

SQL How do I add commit and rollback transaction?

How do I add commit and rollback transaction in the following proc.

ALTER PROCEDURE [dbo].[sp_MoveData_StateTransaction]
as
DECLARE @TitleAppData TABLE(row_Id int identity, title_app_id int)

INSERT INTO @TitleAppData          
      SELECT [title_app_id]
      FROM [t21_maryland_dev].[dbo].[title_app] ta
      where ta.[deal_status_id] = 3 -- finalized status
      and not exists (select 1 from [dbo].[State_Transactions] st where st.[title_app_id] = ta.[title_app_id] and st.transmit_status_id = 2) -- non-sent transactions
 
-- LOOP THRU THE DEALER TABLE
DECLARE @cnt INT = 0;
SET @cnt = (SELECT COUNT(1) FROM @TitleAppData);

DECLARE @rowNum int = 1;
DECLARE @newTransId int = 0;
DECLARE @TransactionName varchar(20) = 'Transaction1';
WHILE (@rowNum <= @cnt)
BEGIN
      BEGIN TRAN @TransactionName
      -- add a transaction object      
      -- get the title_app_id from the temp dealer table...use the row_id to keep track of where you are in the loop
      DECLARE @title_app_id int = (SELECT title_app_id FROM @TitleAppData where row_Id = @rowNum);  
      INSERT INTO [dbo].[State_Transactions]
         ([title_app_id]
            ,[operator_id]
            , location_id
            , record_type_code
            , state_trans_type_code
            , transmit_status_id
            ,[transaction_datetime]
            ,[effective_date])
      SELECT [title_app_id]
                    ,[dealer_id]
                    ,'TEC'
                    , '10' -- this can be 10 or 15 (15 are from the Title_App_Lien table)
                    , 'T3' -- this can be various values
                    , 1 -- pending code
                    ,Getdate()
                    ,Getdate()
      FROM [t21_maryland_dev].[dbo].[title_app] ta
      where ta.title_app_id = @title_app_id
 
      SET @newTransId = @@IDENTITY;      
      print @newTransId

      -- build out the logic to get the rest of the tables
      INSERT INTO [dbo].[State_Transactions_Liens](trans_id)
      VALUES (@newTransId);
      
      INSERT INTO [dbo].[State_Transactions_Vehicles](trans_id, vehicle_vin_old)
      SELECT distinct @newTransId, tv.vin
      FROM [dbo].[title_app_vehicles] tv
      where tv.title_app_id = @title_app_id        
      
      ROLLBACK TRAN @TransactionName;

      -- if failure, roll back the entire title_app_id
      -- else commit
      
      SET @rowNum = @rowNum + 1
END;
ASKER CERTIFIED SOLUTION
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As an aside, you really need to get away from doing one INSERT at a time and instead add all the rows at one time.  As written it has to be real dog.
I don't think its wise idea to initiate transactions for each iteration within loop. Either do it for subsets or do it for whole loop at a time. Something like this:

ALTER PROCEDURE [dbo].[sp_MoveData_StateTransaction]
as
DECLARE @TitleAppData TABLE(row_Id int identity, title_app_id int)

INSERT INTO @TitleAppData          
      SELECT [title_app_id]
      FROM [t21_maryland_dev].[dbo].[title_app] ta
      where ta.[deal_status_id] = 3 -- finalized status
      and not exists (select 1 from [dbo].[State_Transactions] st where st.[title_app_id] = ta.[title_app_id] and st.transmit_status_id = 2) -- non-sent transactions
 
-- LOOP THRU THE DEALER TABLE
DECLARE @cnt INT = 0;
SET @cnt = (SELECT COUNT(1) FROM @TitleAppData);

DECLARE @rowNum int = 1;
DECLARE @newTransId int = 0;
--DECLARE @TransactionName varchar(20) = 'Transaction1';
Begin Tran
      Begin Try
            WHILE (@rowNum <= @cnt)
            BEGIN
                    --BEGIN TRAN @TransactionName
                    -- add a transaction object      
                    -- get the title_app_id from the temp dealer table...use the row_id to keep track of where you are in the loop
                    DECLARE @title_app_id int = (SELECT title_app_id FROM @TitleAppData where row_Id = @rowNum);  
                    INSERT INTO [dbo].[State_Transactions]
                         ([title_app_id]
                              ,[operator_id]
                              , location_id
                              , record_type_code
                              , state_trans_type_code
                              , transmit_status_id
                              ,[transaction_datetime]
                              ,[effective_date])
                    SELECT [title_app_id]
                                          ,[dealer_id]
                                          ,'TEC'
                                          , '10' -- this can be 10 or 15 (15 are from the Title_App_Lien table)
                                          , 'T3' -- this can be various values
                                          , 1 -- pending code
                                          ,Getdate()
                                          ,Getdate()
                    FROM [t21_maryland_dev].[dbo].[title_app] ta
                    where ta.title_app_id = @title_app_id
             
                    SET @newTransId = @@IDENTITY;      
                    print @newTransId

                    -- build out the logic to get the rest of the tables
                    INSERT INTO [dbo].[State_Transactions_Liens](trans_id)
                    VALUES (@newTransId);
                 
                    INSERT INTO [dbo].[State_Transactions_Vehicles](trans_id, vehicle_vin_old)
                    SELECT distinct @newTransId, tv.vin
                    FROM [dbo].[title_app_vehicles] tv
                    where tv.title_app_id = @title_app_id        
                 
                    --ROLLBACK TRAN @TransactionName;

                    -- if failure, roll back the entire title_app_id
                    -- else commit
                 
                    SET @rowNum = @rowNum + 1
            END;
            Commit
      End Try
      Begin Catch
            IF @@TRANCOUNT > 0
            ROLLBACK;
      End Catch