T-SQL: Commit operations of nested stored procedure from outer stored procedure

Question

I am using nested store procedures. Begin transaction and commit/rollback statements are in the outer SP. Can I have all the operations over database, which take place in the nested SP, get committed in the outer SP? Currently seems, that it doesn't work like this. Are there any configs on transactions, which allow doing so?

ALTER procedure [dbo].[OuterStoredProcedure]
as
   begin
       declare @nRC int
       SET NOCOUNT ON

       begin transaction

       execute @nRC=InnerStoredProcedure /*includes update statements*/

        if (@nRC <> 1)
            rollback transaction
        else
            commit transaction
   end

Show us your code. If you are executing SPs inside a transaction, and you roll back, then yes, the "actions" by those SPs will be rolled back. If it isn't working as your expect, then seems like your transaction declarations are wrong. — Thom A, Oct 29 '19 at 10:15
So what's the problem here? By point about stands: *" If you are executing SPs inside a transaction, and you roll back, then yes, the "actions" by those SPs will be rolled back."* Perhaps the problem is that you have `@nRC <> 1`? An SP returns `0` for success, not `1`. — Thom A, Oct 29 '19 at 10:59
[DB<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ce5e4e75a0303162332d0f8b99174b01) — Thom A, Oct 29 '19 at 11:07
@Larnu, assume that it returns 1 for success. The main question is why can't commit in the outer procedure be applied to update statement in the inner procedure? I guess when we call another procedure, some internal transaction is started and we can't use the same transaction across multiple nested stored procedures. — Karen Gumerov, Oct 29 '19 at 11:13
*"assume that it returns 1 for success."* but they don't, that's my point. The value `0` for a return code from an SP specifically means "Success"; any other code means "failure" and I suspect that is why you are rolling back the tranasction; as the value is `0`, which isn't `1`. *"The main question is why can't commit in the outer procedure be applied to update statement in the inner procedure?"* It can, as shown in the Fiddle in my above comment. — Thom A, Oct 29 '19 at 11:24
Right, I must have been looking in a wrong direction. Thanks. — Karen Gumerov, Oct 29 '19 at 11:44

Ardalan Shahgholi · Answer 1 · 2019-11-05T16:15:29.453

I have prepared below test for you. As you can see, If nested procedure returns 0 (as error) we can do rollback in first procedure (parent procedure)

CREATE TABLE test1010
(
    ID Int identity (1,1),
    Name nvarchar(20)
)
GO

--DROP PROCEDURE dbo.A1
CREATE PROCEDURE dbo.A1
    @name nvarchar(20)
AS
    BEGIN
        INSERT INTO test1010 VALUES (@name)
        return 0
    END
GO

--DROP PROCEDURE dbo.AA
CREATE PROCEDURE dbo.AA
    @name1 nvarchar(20)
AS
    BEGIN
        DECLARE @nRC INT;
        SET NOCOUNT ON;

        BEGIN TRANSACTION;
        EXECUTE @nRC = dbo.A1 @name = @name1;
        IF(@nRC <> 1)
            ROLLBACK TRANSACTION;
        ELSE
            COMMIT TRANSACTION;
    END;
GO


SELECT * FROM test1010
GO
EXECUTE dbo.AA  @name1 = 'aa'
GO
SELECT * FROM test1010

And there is an other things. In each procedure we have to check number of transaction. If we don't have a transaction we open it, if we have we save it. At the end we check, if we opened the transaction, we commite it if not we let parent procedure to work on transaction.

You can see my answerhere.

CREATE PROCEDURE Ardi_Sample_Test  
    @InputCandidateID INT  
AS  
    DECLARE @TranCounter INT;  
    SET @TranCounter = @@TRANCOUNT;  
    IF @TranCounter > 0  
        SAVE TRANSACTION ProcedureSave;  
    ELSE  
        BEGIN TRANSACTION;  
    BEGIN TRY  

        /*
        <Your Code>
        */

        IF @TranCounter = 0  
            COMMIT TRANSACTION;  
    END TRY  
    BEGIN CATCH  
        IF @TranCounter = 0  
            ROLLBACK TRANSACTION;  
        ELSE  
            IF XACT_STATE() <> -1  
                ROLLBACK TRANSACTION ProcedureSave;  

        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    END CATCH  
GO

Use always this pattern in your procedures.

Wouldn't it be better to just `THROW` in the `CATCH` block instead of all this code to re-raise an error? — Gert Arnold, Nov 05 '19 at 20:54
@GertArnold Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR. You have right — Ardalan Shahgholi, Nov 05 '19 at 21:38

T-SQL: Commit operations of nested stored procedure from outer stored procedure

1 Answers1