CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
user4221591
user4221591

Reputation: 2150

rollback whole txn when any of the insert statement fails in stored procedure

I've a stored procedure where there are two insert statement for two different tables. 

create procedure p_multiple_insert
(
// variable declaration
)
declare @temp int = 0;
begin try
    begin 
    // insert statement for TABLE1
        set @temp = 1;
    if(@temp = 1)
        begin
            // insert statement for TABLE2
        end
    end
end try
begin catch
// insert into error table error_number() & error_message()
end catch

If the error occurs with first insert statement block, @temp = 0. So second insert block does not execute and data is also not inserted in the TABLE1.

But if the error occur while inserting into TABLE2, how can I rollback my whole transaction. I mean roll back first insert statment too.

Upvotes: 1

Views: 2612

Answers (1)

user4622594
user4622594

Reputation:

my changes are in UPPER CASE:

create procedure p_multiple_insert
(
-- variable declaration
)
declare @temp int = 0;

BEGIN TRANSACTION

begin try
    -- insert statement for TABLE1
        set @temp = 1;
    if(@temp = 1)
        begin
            -- insert statement for TABLE2
        end
    end

    COMMIT TRANSACTION

end try
begin catch

    ROLLBACK TRANSACTION

    -- insert into error table error_number() & error_message()
end catch
  1. BEGIN TRANSACTION at the beginning
  2. COMMIT at the end of try block
  3. ROLLBACK everything in catch-block

My "Standard-Catch-Block" always looks like this:

    DECLARE @errmsg NVARCHAR(MAX)
    SELECT  @errmsg = 'Error executing dbo.StoredProcName: ' 
                         + COALESCE(ERROR_MESSAGE(),'No Message from SQL Server')
    RAISERROR(@errmsg,16,1)
    ROLLBACK TRANSACTION

Upvotes: 1

Related Questions