How to rollback when 3 stored procedures are started from one stored procedure

I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful.

If the first stored procedure works fine in the master stored procedure, but the 2nd stored procedure fails, then will it automatically roll back all the SP's in the master SP or do I have to make some command?

Here is my procedure:

CREATE PROCEDURE [dbo].[spSavesomename] 
    -- Add the parameters for the stored procedure here

    @successful bit = null output
AS
BEGIN
begin transaction createSavebillinginvoice
    begin Try
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   BEGIN 

   EXEC [dbo].[spNewBilling1]

   END

   BEGIN 

   EXEC [dbo].[spNewBilling2]

   END

   BEGIN 

   EXEC [dbo].[spNewBilling3]

   END 

   set @successful  = 1

   end Try

    begin Catch
        rollback transaction createSavesomename
        insert into dbo.tblErrorMessage(spName, errorMessage, systemDate) 
             values ('spSavesomename', ERROR_MESSAGE(), getdate())

        return
    end Catch
commit transaction createSavesomename
return
END

GO

Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will be caught and the ROLLBACK in the CATCH block will roll back all of the work.

BUT here are some things to note about transactions (at least in SQL Server):

-
There is only ever one real transaction (the first one), no matter how many times you call BEGIN TRAN

• You can name a transaction (as you have done here) and that name will appear in the logs, but naming only has meaning for the first / outer-most transaction (because again, the first one is the transaction).

• Each time you called BEGIN TRAN, whether or not it is named, the transaction counter is incremented by 1.

• You can see the current level by doing SELECT @@TRANCOUNT;

• Any COMMIT commands issued when @@TRANCOUNT is at 2 or above do nothing more than reduce, one at a time, the transaction counter.

• Nothing is ever committed until a COMMIT is issued when the @@TRANCOUNT is at 1

• Just in case the information above does not indicate clearly: regardless of the transaction level, there is no actual nesting of transactions.

-
Save points allow for creating a subset of work within the transaction that can be undone.

• Save points are created/marked via the SAVE TRAN {save_point_name} command

• Save points mark the beginning of the subset of work that can be undone without rolling back the entire transaction.

• Save point names do not need to be unique, but using the same name more than once still creates distinct save points.

• Save points can be nested.

• Save points cannot be committed.

• Save points can be undone via ROLLBACK {save_point_name}. (more on this below)

• Rolling back a save point will undo any work that happened after the most recent call to SAVE TRAN {save_point_name}, including any save points created after the one being rolled-back was created (hence the "nesting").

• Rolling back a save point has not effect on the transaction count/level

• Any work done prior to the initial SAVE TRAN cannot be undone except by issuing a full ROLLBACK of the entire transaction.

• Just to be clear: issuing a COMMIT when @@TRANCOUNT is at 2 or above, has no effect on save points (because again, transaction levels above 1 don't exist outside of that counter).

-
You cannot commit specific named transactions. The transaction "name", if provided along with the COMMIT, is ignored and only exists for readability.

-
A ROLLBACK issued without a name will always rollback ALL transactions.

-
A ROLLBACK issued with a name must correspond to either:

• The first transaction, assuming it was named:

Assuming no SAVE TRAN has been called with the same transaction name, this will rollback ALL transactions.

• A "save point" (described above):

This behavior will "undo" all changed made since the most recent SAVE TRAN {save_point_name} was called.

• If the first transaction was a) named and b) has had SAVE TRAN commands issued with its name, then each ROLLBACK of that transaction name will undo each save point until there are none left of that name. After that, a ROLLBACK issued of that name will rollback ALL transactions.

-
For example, assume the following commands were run in the order shown:

BEGIN TRAN A -- @@TRANCOUNT is now 1
-- DML Query 1
SAVE TRAN A
-- DML Query 2
SAVE TRAN A
-- DML Query 3

BEGIN TRAN B -- @@TRANCOUNT is now 2
SAVE TRAN B
-- DML Query 4

Now, if you issue (each of the following scenarios is independent of each other):

• ROLLBACK TRAN B once: It will undo "DML Query 4". @@TRANCOUNT is still 2.

• ROLLBACK TRAN B twice: It will undo "DML Query 4" and then error as there is no corresponding save point for "B". @@TRANCOUNT is still 2.

• ROLLBACK TRAN A once: It will undo "DML Query 4" and "DML Query 3". @@TRANCOUNT is still 2.

• ROLLBACK TRAN A twice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". @@TRANCOUNT is still 2.

• ROLLBACK TRAN A thrice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". Then it will rollback the entire transaction (all that was left was "DML Query 1"). @@TRANCOUNT is now 0.

• COMMIT once: @@TRANCOUNT goes down to 1.

• COMMIT once and then ROLLBACK TRAN B once: @@TRANCOUNT goes down to 1. Then it will undo "DML Query 4" (proving that COMMIT didn't do anything) . @@TRANCOUNT is still 1.

-
Transaction names and save point names:

• can have up to 32 characters

• are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.

• For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers

-
A stored procedure is not, in itself, an implicit transaction. Each query if no explicit transaction has been started, is an

StackExchange Database Administrators Q#82681, answer score: 66