1

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.

SQLBoffin
  • 21
  • 4
  • Does the question say anywhere what the context of this is? Are any of the inserts supposed or expected to fail (on a unique constraint violation, for example)? (If the question is literally "when the SP is run", it's quite poorly worded -- any chance it's literally asking what the value is just *before* the procedure runs, if it supplies any context on that?) Also, does it make any mention of the value of `XACT_ABORT`? – Jeroen Mostert Jul 23 '19 at 10:28
  • Hi Jeroen, the only thing provided in the question is this ' A stored procedure is used to insert sales invoice into the dbo.salesinvoice. You run the stored procedure that is shown in the exhibit. What is the value of @@trancount ? Poorly worded isn't it. This is third question that i'm contesting reviewing my practice exam. Might have ask for my money back. – SQLBoffin Jul 23 '19 at 10:34
  • 2
    Is `Rollback Trnsaction` a typo on your end, or is that actually in the question? – Jeroen Mostert Jul 23 '19 at 10:38
  • Also, if this procedure "is used to insert sales invoice in `dbo.salesinvoice`", why is it inserting in `TestTable1`? If you're paraphrasing the question, please don't -- post it exactly, or else at least very clearly indicate which identifiers were changed for copyright purposes (if that's really a concern for so small a snippet). The devil's in the details when it comes to error handling, and for T-SQL [there are a lot of details](http://sommarskog.se/error_handling/Part2.html). – Jeroen Mostert Jul 23 '19 at 10:50
  • Apologies, typo and paraphrasing, i'll amend. This is quite annoying though, I have only seen positive reviews for the measure up exams but didn't expect to have questions with such a high level ambiguity in them. – SQLBoffin Jul 23 '19 at 10:52
  • With the code as posted, I cannot completely exclude the possibility of some combination of settings and triggers on the tables that might cause a non-zero `@@TRANCOUNT` after execution, but I can't imagine such a scenario either. With a normal run there is no reason to assume `@@TRANCOUNT` will be anything but 0. Had the code not used `BEGIN TRY` it would be much easier to produce scenarios where `@@TRANCOUNT` ends up non-zero despite a `ROLLBACK`, but it does, so... – Jeroen Mostert Jul 23 '19 at 11:13

0 Answers0