Can we maintain multiple transactions inside a procedure?
Multiple/ nested transaction can be maintained by the transaction inside a transaction, below is the sample for the statement for the same, below is the diagram which explains, if we have nested transaction how are they executed: i.e. first transaction 3 will be committed, post that transaction 2, at the end transaction 1:
Below is the example for the same
create PROC usp_transaction_handle_multiple
AS
BEGIN
BEGIN try
BEGIN transaction trans1
INSERT INTO input_values
SELECT 'One', 'Two'
BEGIN try
BEGIN transaction trans2
IF (( SELECT count(1) FROM input_values where col1='One') < 2>
BEGIN
UPDATE input_values SET col2 ='updated two' WHERE col1='One'
COMMIT transaction trans1
PRINT 'Commit trans2 done'
END
ELSE
BEGIN
RAISERROR('No data to update',16,1)
END
END try
BEGIN CATCH
ROLLBACK transaction trans2
PRINT 'ROLLBACK trans2 done'
END CATCH
IF (@@TRANCOUNT <> 0)
BEGIN
COMMIT transaction trans1
PRINT 'Commit trans1 done'
END
END try
BEGIN CATCH
ROLLBACK transaction trans1
PRINT 'ROLLBACK trans1 done'
END CATCH
END
Executing statemnets:
select * from input_values
go
exec usp_transaction_handle_multiple
go
select * from input_values