70-761 Practice exam Question about @@Trancount final value

There is this question in the practice 70-761 exam that states after executing the following script, what is the value of @@Trancount?

Exam script:

CREATE PROCEDURE dbo.up_CreateSalesInvoice
(
    @Date DATE,
    @CustomerID INT,
    @StockitemId INT,
    @Quantity INT,
    @UnitPrice DECIMAL(8, 2),
    @InvoiceID INT OUT
)
AS
BEGIN
    DECLARE @RETVAL INT;
    BEGIN TRANSACTION;
    BEGIN TRY

        BEGIN TRANSACTION;
        INSERT INTO dbo.SalesInvoice(InvoiceDate, CustomerID)
            VALUES (@Date, @CustomerID);
        SET @InvoiceID = SCOPE_IDENTITY();
        COMMIT TRANSACTION;

        BEGIN TRANSACTION;
        INSERT INTO dbo.SalesInvoiceLine(InvoiceID, StockItemID, Quantity, UnitPrice)
            VALUES (@InvoiceID, @StockItemID, @Quantity, @UnitPrice);
        COMMIT TRANSACTION;

        COMMIT TRANSACTION;
        SET @RETVAL = 0;
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @RETVAL = 1;
    END CATCH

    RETURN @RETVAL;
END;

The answer I came into conclusion is 0, but the expected answer is 1, so i moved to replicate the script behavior with the following script, and the result returned from @@Trancount is 0, unless my script is doing something different with the nested transactions if anyone can explain if the expected answer is wrong or if my logic emulating the nested transaction of the script i got something wrong witch gives me a different result

Script emulating the behavior:

```
CREATE TABLE #TBL(
ID INT,
TANK VARCHAR(12)
)

INSERT INTO #TBL VALUES (1, 'T-55-MA')

PRINT 'TRANCOUNT INICIAL :: ' + CAST(@@TRANCOUNT AS VARCHAR)

BEGIN TRANSACTION -- +1
PRINT 'BEGIN TRANSACTION 1 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
BEGIN TRY
BEGIN TRANSACTION -- +1
PRINT 'NESTED BEGIN TRANSACTION 1 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
INSERT INTO #TBL VALUES (2, 'M4A3-W(76)')
COMMIT TRANSACTION -- -1
PRINT 'NESTED COMMIT 1 TRANSACTION _ TRANCOUNT :: ' + CAST(

The expected answer provided in the practice exam is incorrect. Microsoft's own documentation spells out clearly the same thing you've found - every BEGIN TRANSACTION increments @@TRANCOUNT by 1, every COMMIT TRANSACTION decrements it by 1 and ROLLBACK TRANSACTION resets @@TRANCOUNT to 0.

Nested transactions count and increment the @@TRANCOUNT value by 1 as well, so the statement that SQL Server doesn't actually create a nested transaction is also false.

See here for more info. It also does not make a difference if the code is in a stored procedure or not.

StackExchange Database Administrators Q#238425, answer score: 5