SQLBoffin
SQLBoffin

Reputation: 21

Nested transactions and @@trancount count (issue with the practice question for 70-761)

Got a practice question from Measure up and not sure if it is badly worded or I'm missing something regarding nested transactions.

Basically gives me a definition of a stored procedure and states

When the sp is run, what is the value of @@trancount?

I get that SQL Server only cares about the outer transaction but @@trancount should be 0 since everything is committed and if it fails everything is rolled back which would still be 0 but it is telling me it should be 1.

It doesn't specify in the code where the @@trancount is run but the wording suggest it is run after the sp is executed.

I ran the sp with some dummy data with @@trancount at the end and got 0.

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 ;

Expect @@trancount to be 0 as there are no open transactions for it to count.

Upvotes: 1

Views: 227

Answers (0)

Related Questions