Reputation: 21
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