Solved

SQL How do I add commit and rollback transaction?

Posted on 2013-01-04
3
457 Views
Last Modified: 2013-01-10
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;
0
Comment
Question by:Meinhoonaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 38745748
T-SQL has a Try-Catch block now
BEGIN TRY

	BEGIN TRANSACTION;

	-- put your code here

	COMMIT TRANSACTION;

END TRY
BEGIN CATCH

	IF @@TRANCOUNT > 0
		ROLLBACK;

END CATCH

Open in new window

any errors inside the Try drop into the Catch block you then you can roll it back, no errors make it to the Commit statement.  You can try putting this inside your WHILE instead of the regular BEGIN block.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38748797
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.
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38751184
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
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question