1

I have a test that calls a stored procedure. I expect the stored procedure to reject the input supplied, with a raiserror. The relevant part of my tSQLt test is:

EXEC tSQLt.ExpectException @ExpectedMessagePattern = '%more than one subcategory%';
EXEC usp_add_rfx_rfx lots, of, parameters, here

The test result from tSQLt depends on the severity in the raiserror in the usp_add_rfx_rfx sp.

If the severity is 12 or more, the test fails with this message:

[test_RFX_configuration].[test_multiple_categories_and_lots] failed: (Error) Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.[16,2]{test_multiple_categories_and_lots,0} (There was also a ROLLBACK ERROR --> The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.{Private_RunTest,149})

If the severity is less, the test fails like this:

[test_RFX_configuration].[test_multiple_categories_and_lots] failed: (Failure) Expected an error to be raised.

I've googled the issue and tried a number of things I found - using TRY-CATCH etc but no joy. Any ideas?

Resource
  • 524
  • 4
  • 16

1 Answers1

1

That error looks like the sproc under test (ups_add_rfx_rfx) has it's own transaction handling built in. There is similar question on stack overflow How to ROLLBACK a transaction when testing using tSQLt and on that question Sebastien, one of tSQLt's authors links to one of his own blog posts on one approach to address that issue how to rollback in procedures.

Community
  • 1
  • 1
datacentricity
  • 1,099
  • 9
  • 15
  • Thanks for the suggestion, datacentricity. I've already read both the question and the article linked to, and explored Sebastien's suggestions. I've tried every one of the answers suggested in the question! ups_add_rfx_rfx does have transaction handling, but the raiserror happens before the BEGIN TRANSACTION. I removed the transaction from usp_add_rfx_rfx for analysis and it didn't fix the problem. My conclusion is that the tSQLt transaction is directly impacted by the raiserror. – Resource Jun 09 '16 at 15:27
  • Your best option is to post the code - both the sproc (or a scaled down version that still presents the error) and the test then perhaps we can get to the bottom of the problem - or find a work around. I use a slightly different patter to that originally suggested by Sebastien (IIRC) so if you post your code I will try and recreate the problem in my environment and see if my pattern solves the problem – datacentricity Jun 09 '16 at 18:43
  • datacentricity - when I went back and checked, I realised that in the midst of all my fixes the transactions were turned on again at the end. – Resource Jun 10 '16 at 15:48
  • An extra rollback was happening in my catch block as you predicted. I separated the try-catch blocks - one for preconditions - one for transaction code. After this change the ExpectException is satisfied in my test. It's still not quite right - the actual exception thrown is not the one I expect, possibly because of a logging function in the middle. That's ok. Running an ExpectNoException test first, I can then change only the relevant parameter and verify an exception is thrown. – Resource Jun 10 '16 at 15:57