Nested Transaction in nested stored procedures

Question

Actually, I got a little confused about using nested transaction in stored procedures. I need to call the two/three procedures with in one main procedure.

Eg:

Begin try

Begin Tran -- 1st level transcation

Procedure 1 --will be executed

Procedure 2 --will be executed

End tran -- 1st level transcation
End try
Begin Catch
Rollback tran
End Catch


PROCEDURE 1:

BEGIN TRAN TRAN1
///scripts
COMMIT TRAN TRAN1


PROCEDURE 2:

BEGIN TRAN TRAN2
///scripts
COMMIT TRAN TRAN2

Am I doing it in a right way?

Answer 1

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

Please refer the link https://technet.microsoft.com/en-us/library/ms189336%28v=sql.105%29.aspx

Answer 2

There is no such thing as nested transactions. If you want to rollback just one part of transaction then I should use savepoints. Regarding savepoints, if after COMMIT TRANSACTION SavepointName is executed ROLLBACK this last ROLLBACK statement will rollback everythint including section defined by savepoint:

BEGIN TRANSACTION -- A

SAVE TRANSACTION SavePoint1 -- B
-- do something
COMMIT TRANSACTION SavePoint1 -- C

-- ...
-- This ROLLBACK will rollback everything including data committed for SavePoint1 - it will rollback also actions for [B,C] range
ROLLBACK -- D

If I would use savepoints then I would use following template from here (see section Example).

Answer 3

BEGIN TRANSACTION => @@TRANCOUNT = @@TRANCOUNT + 1
COMMIT TRANSACTION => @@TRANCOUNT = @@TRANCOUNT - 1 (if 0 save changes)
ROLLBACK TRANSACTION => @@TRANCOUNT = 0 (and discard changes)

In your outer procedure just do

BEGIN TRY
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    ..
END TRY

Answer 4

I do the whole process with two hypothetical procedures. To fully test and use it. I hope it works out after this time, because the question is three years ago.

1.Fist step Create two table :

CREATE TABLE [dbo].[Products](
    [ProductId] [nvarchar](70) NOT NULL,
    [ItemId] [nvarchar](50) NOT NULL,
    [Color] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_table_4] PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](20) NULL,
    [NewColumn] [nchar](10) NULL,
 CONSTRAINT [P_Users_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

2. Create 3 stored procedure (ProductID not identity and i don't used it for generate test error):

--SP1
Create PROCEDURE [dbo].[usp_adduser]
    @name nvarchar(20),
    @new  nchar(10)
AS
BEGIN

    SET NOCOUNT ON;

    insert into users
    values (@name,@new)
END
GO
--SP2
Create PROCEDURE [dbo].[usp_addproduct]
    @itemid nvarchar(50),
    @color  nvarchar(50)
    --,@Productid int = null --for test commnted

AS
BEGIN

    SET NOCOUNT ON;

    insert into products ([ItemId], [Color])    --dont use producid for test
    values (@itemid,@color)
END
GO
--SP3
Create PROCEDURE [dbo].[usp_addmix]
    @itemid nvarchar(50),
    @color  nvarchar(50),
    @name   nvarchar(20),
    @new    nchar(10)

AS


    SET NOCOUNT ON;
    Set Implicit_transactions ON;

    Begin transaction

            Execute usp_adduser @name,@new

            Execute usp_addproduct @itemid,@color

            Commit transaction
    Catch
        if @@TRANCOUNT > 0 
        rollback

3. entry false data

EXEC    [dbo].[usp_addmix]
        @itemid = N'1',
        @color = N'red',
        @name = N'ali',
        @new = N'test'

Its work well.

When the first procedure is correct and the latter error, or vice versa, the rollback operation works well for both.