Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL How do I add commit and rollback transaction?

Posted on 2013-01-04
3
Medium Priority
?
461 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

877 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