Solved

SQL How do I add commit and rollback transaction?

Posted on 2013-01-04
3
451 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 26

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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