During development of stored procedures, i ran into one issue and this was due to not applied best practice of stored procedures practice and this issue comes mostly when we call stored procedures inside another one and so on, which are used in nested in transactions.
So i would like to write next post for the stored procedures development , as how we should write it, It will be a just a template of best practice. In this post i will explain the details of the issue which i suffered and how can we resolve it.
Let's for the demonstration we will created one table and stored procedures.
-- Creating table which will be used in SPs.
CREATE TABLE tbl_Tran
(
TranId INT NOT NULL PRIMARY KEY
,TranName VARCHAR(10)
)
GO
-- Creating first stored procedure.
CREATE PROCEDURE Firttranproc
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
-- Inserting records in table,
-- Due to this insert error will be raised.
INSERT INTO tbl_Tran
(TranId
,TranName)
SELECT
1
,'Tran-1'
UNION ALL
SELECT
1
,'Tran-1'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Rollback Tran1'
END CATCH
END
GO
-- Creating Second stored procedure
CREATE PROCEDURE Secondtranproc
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO tbl_Tran
(TranId
,TranName)
SELECT
2
,'Tran-2'
-- Calling first created stored procedure here.
EXEC Firttranproc
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Rollback Tran2'
END CATCH
END
GO
-- Executing second stored procedure here and see what happen.
EXEC Secondtranproc
GO
In first stored we have written a code to raise error and due to transaction issue while running second stored procedure it raised error.
This is not best practice as we have write only "Begin Transaction". We should specify the name of the stored procedure.
# 1 Solution :
Let's specify the name of transactions in both of the stored procedures and altering stored procedures here and execute it again with same.
-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
BEGIN
SET NOCOUNT ON
-- Here we have specified Tran1 as transaction name
BEGIN TRY
BEGIN TRANSACTION Tran1
INSERT INTO tbl_Tran
(TranId
,TranName)
SELECT
1
,'Tran-1'
UNION ALL
SELECT
1
,'Tran-1'
COMMIT TRANSACTION Tran1
END TRY
BEGIN CATCH
PRINT 'Rollback Tran1'
ROLLBACK TRANSACTION Tran1
END CATCH
END
GO
-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
BEGIN
SET NOCOUNT ON
-- Here we have specified Tran2 as transaction name
BEGIN TRY
BEGIN TRANSACTION Tran2
-- Inserting records
INSERT INTO tbl_Tran
(TranId
,TranName)
SELECT
2
,'Tran-2'
-- Calling first stored procedure here
EXEC Firttranproc
COMMIT TRANSACTION Tran2
END TRY
BEGIN CATCH
PRINT 'Rollback Tran2'
ROLLBACK TRANSACTION Tran2
END CATCH
END
GO
-- Executing second stored procedure which will also call of SP1
EXEC Secondtranproc
GO
You can see here the error is not raised which is raided in first case. Here stored procedure find and commit/rollback correct and their transactions only.
# 2 Solution :
Le's look another alternative method which can also help to come out from issue.
The rewritten stored procedures,
-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
BEGIN
SET NOCOUNT ON
-- Here we have specified Tran1 as transaction name
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO tbl_Tran
(TranId
,TranName)
SELECT
1
,'Tran-1'
UNION ALL
SELECT
1
,'Tran-1'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Tran1'
-- This statement first check open transaction for their session
-- If found then will rollback it.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END
GO
-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
-- Inserting records
INSERT INTO tbl_Tran
(TranId
,TranName)
SELECT
2
,'Tran-2'
-- Calling first stored procedure here
EXEC Firttranproc
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Tran2'
-- This statement first check open transaction for their session
-- If found then will rollback it.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END
GO
-- Executing second stored procedure which will also call of SP1
EXEC Secondtranproc
GO
Here is same output as second case , it is also not raised error.
Let me know what you think over here. You also encountered this issue in past, Please share you experience and alternation solution.