We've got a situation where the SSDT for VS 2013 is working well in 90% of scenarios but failing where the stored procedures being tested rollback transactions.
Because SQL Server doesn't really have nested transactions, rolling back an inner transaction rolls back any outer transactions. Normally this isn't an issue but within the context of the SSDT unit test we're taking a slightly customised approach by wrapping unit test methods in a TransactionScope / rollback. This is what causes the problem, whilst solving issues relating to unit test data cleansing post test.
To get around this when a condition occurs in our procedures which requires a rollback, we add a BEGIN TRANSACTION just prior to the end of the procedure. This balances out the transaction count, and with some testing we did to check the pre and post procedure exec it seems to ensure the implicit COMMIT on that last BEGIN is completed.
The question is: under normal operation (i.e. not being surrounded by a TransactionScope in the calling middleware), it seems like this opens up issues with any statements following the stored procedure exec in the same batch. Is there an issue when calling the stored procedure from ORM's such as EF or directly with ADO.NET? Surely that statement batch would terminate leaving implicit completion of the opened transaction?
For example / reference the stored procedure would contain something like this:
CREATE PROCEDURE [dbo].[ProcName]
/* params */
AS
SET NOCOUNT ON
SET XACT_ABORT ON
/* set var according to transcount check*/
DECLARE @IsInsideTransaction BIT = CASE WHEN @@trancount > 0 THEN 1 ELSE 0 END
/* apply new information to data store */
begin TRANSACTION
declare @RetVal int = 0
begin try
insert into dbo.AMessageTable
(/* some columns */)
values
(/* some values */)
set @messageId = scope_identity()
commit transaction
set @retVal = @messageId
end try
begin catch
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
SET @RetVal = -1;
ROLLBACK TRANSACTION
/* ### this gets us out of the transaction count issue within the UT ### */
IF @IsInsideTransaction = 1
BEGIN
BEGIN TRANSACTION
END
end catch
return @retVal
GO
And we modify the auto-generated SSDT unit test code to include something like this:
[TestInitialize()]
public void TestInitialize() {
_transaction = new TransactionScope(TransactionScopeOption.RequiresNew);
base.InitializeTest();
}
[TestCleanup()]
public void TestCleanup() {
base.CleanupTest();
if (_transaction != null) {
_transaction.Dispose();
}
}
It seems to me that you have two problems.
The first is that you are mixing in code to make your test work in your production code. This is generally seen as bad practice as it means that you either need to run your testing code on production or modify the stored procedure when you deploy - in which case you are not testing the code you are deploying which is risky.
The second is that to get your tests to work reliably you are creating a strange mix of transactions and that is adding quite a lot of complexity.
Have you looked at tSQLt, specifically look at creating Fakes which mean that you create a fake table, insert some test data and then call your stored procedure. tSQLt takes care of rolling back any changes so you end up with the database in the same state as it was before the test, but if you use fake tables properly, it doesn't matter what state it is in before and after the tests.