?
Solved

SQL How do I add commit and rollback transaction?

Posted on 2013-01-04
3
Medium Priority
?
467 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
3 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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