SpyProcedure fails when paired with a RemoveObject

[vkuharsky]

I have a test where I need to mock a bunch of validation SPs and also want to replace one SP with a mock that should just throw an arbitrary exception to imitate SQL error during code execution. Everything was looking fine until that test case was executed.

After some code tweaks inside the test itself, I've found that after the actual code execution of the tested procedure _SpyProcedureLog table for my mock magically disappeared and is no longer available for the later portion of the test.

Here is an example code that illustrates what is going on inside the described test case:

CREATE PROC TestClass.[test application locks]
AS
-- Faking a dozen tables by using tSQLt.FakeTable
-- Faking a dozen small SProcs using tSQLt.SpyProcedure

EXEC tSQLt.SpyProcedure 'dbo.Entity_Lock';
EXEC tSQLt.SpyProcedure 'dbo.Entity_Release';

-- Replacing an object of interest by using a documented approach
EXEC tSQLt.RemoveObject 'dbo.usp_NeedToThrow';
EXEC('CREATE PROC dbo.usp_NeedToThrow AS RAISERROR(''This is a test'', 16, 1);');


EXEC tSQLt.ExpectException @ExpectMessage = 'This is a test';
-- Running my SP
EXEC dbo.usp_TestMePlease 'Param1', 'Param2';

-- Asserting the results 
IF NOT EXISTS ( SELECT 1
                FROM dbo.Entity_Release_SpyProcedureLog )
    BEGIN
        EXEC tSQLt.Fail 'Release was not called properly';
    END

RETURN 0;

When I run this test without TRY CATCH BLOCK wrapping a EXEC dbo.usp_TestMePlease ...,
I'm getting an error: TestClass.[test application locks] failed: : (Error) Message: Cannot release the application lock (Database Principal: 'public', Resource: 'Resource.NameIt') because it is not currently held. | Procedure: xp_userlock (1) | Severity, State: 16, 1 | Number: 1223 (There was also a ROLLBACK ERROR --> Message: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. | Procedure: Private_RunTest_TestExecution (154) | Severity, State: 16, 1 | Number: 3903)
That message obviously shows that the actual code uses the non-mocked version of dbo.Entity_Release. This clue was confirmed a bit later when I ran my SP within the TRY ... CATCH block. Then the test failed with another error: (Error) Message: Invalid object name 'dbo.Entity_Release_SpyProcedureLog'

SQL Server version: 12.0.2269.0 (MS SQL Server 2014 Express Edition)
tSQLt version: 1.0.8083.3529

[mbt1]

Is it possible that the procedure under test issues a rollback, explicitly it implicitly?

[vkuharsky]

Yes, you're right.
Inside of the tested procedure, I do rollback in case of error.
Here is an example structure of how I do that:

CREATE PROC dbo.usp_TestMePlease @Param1 INT, @Param2 INT
AS
    SET NOCOUNT,
        XACT_ABORT ON;
-- Skip other parts of a prolog
-- ...
EXEC dbo.Entity_Lock;
BEGIN TRY
-- Do some pre-data writing checks
-- ...
BEGIN TRANSACTION
-- Do data modifications
COMMIT TRANSACTION
EXEC dbo.Entity_Release;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END
  EXEC dbo.Entity_Release;
  THROW;
END CATCH

RETURN 0;

[vkuharsky]

After tinkering a bit longer, I've managed to make this test passable. The problem, though, is obvious. While semantically correct, the code above is not intended to be run inside another (outer) transaction. I designed this code to be a top-level data API consumable only by external clients, such as services hosting applications. 

Is it possible to make such tests passable without changing how the code works with transactions?

[mbt1]

There are two possible solutions. The one is similar to what you have done. Change the procedure to use a transaction save-point in your procedure. I wrote about that a while ago here: https://sqlity.net/en/585/how-to-rollback-in-procedures/
This preserves your intended functionality without colliding with pre-existing transactions.

The technique above is the preferred architectural solution, because it solves other problems that you might run into, too. But if that change is not possible, there is the other solution. It is an advanced tSQLt technique that runs the test without initiating a transaction. This is fairly new functionality in tSQLt and not yet well documented. I'll attempt to write something up this weekend.

[vkuharsky]

I'm very interested to see another way to test things.

[mbt1]

Did not get it done this weekend. I’ll keep working on it.

…

________________________________ From: Volodymyr Kuharsky ***@***.***> Sent: Wednesday, February 28, 2024 2:02:49 AM To: tSQLt-org/tSQLt ***@***.***> Cc: Sebastian Meine ***@***.***>; Comment ***@***.***> Subject: Re: [tSQLt-org/tSQLt] SpyProcedure fails when paired with a RemoveObject (Issue #198) Yes, you're right. Inside of the tested procedure, I do rollback in case of error. Here is an example structure of how I do that: CREATE PROC dbo.usp_TestMePlease @param1 INT, @Param2 INT AS ... EXEC dbo.Entity_Lock; BEGIN TRY -- Do some pre-data writing checks -- ... BEGIN TRANSACTION -- Do data modifications COMMIT TRANSACTION EXEC dbo.Entity_Release; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXEC dbo.Entity_Release; THROW; END CATCH RETURN 0; — Reply to this email directly, view it on GitHub<#198 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AACIYIIZ67J2KC4A3CV7Z7LYV3JBTAVCNFSM6AAAAABD4GICVCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNRYGM2TGNZSGE>. You are receiving this because you commented.Message ID: ***@***.***>

[vkuharsky]

It's okay, I'll wait. Just post a link with the actual article after you publish it, or let me know somehow.

[vkuharsky]

@mbt1 have you done something about that new functionality? If you still do not have enough time, can you just point to a set of stored procedures inside the tSQLt project?