tSQLt test problem for some DDL queries

Question

I am a SQL Server DBA. To automate some of my daily tasks, I need to write stored procedures. Creating functionality without testing does not make sense to me. For creating and running my tests, I used the tSQLt Framework.

I had to create a database during the test. "CREATE DATABASE" is not permitted inside a multi-statement transaction, but all tests are automatically run inside a transaction within tSQLt Framework.

Naturally, I can set up a test database manually before running the test, but the test cannot depend on the environment in which it will be executed.

How should this be approached?

Answer 1

The newest version of tSQLt allows for running tests outside of a transaction. The documentation is a little lacking (as in: it has not yet been written), but basically you just need to add a @tSQLt:NoTransaction annotation to the test.

Here is an example from the tests for tSQLt itself:

--@tSQLt:NoTransaction('MyInnerTests.TestCleanUp')

The single parameter is a procedure that will be executed after the test, even if the test itself errored or failed. You should use it to clean up after yourself. Keep in mind that there is not transaction protecting you, so you need to carefully undo everything you change in the database / on the server. tSQLt will cleanup test case doubles itself, everything else is on you.

Also keep in mind that your tests should be idempotent, meaning it needs to be able to execute no matter if the database it is trying to create exists already or not, so if that logic is not part of your tested procedure anyway, you need to add logic to the test to drop the database if it exists. This leads to the next thing to think about: You need to write your test in a way that minimizes the probability of damage if executed by an inexperienced coworker. That means in this context, don't use a database name that is used in production for these tests.

--[@tSQLt:NoTransaction]('MyTests.test1CleanUp')
CREATE PROCEDURE MyTests.[test1]
AS
BEGIN
  --Assemble

  --Act

  --Assert
  EXEC tSQLt.Fail 'More work to do here!';
END;

GO

CREATE PROCEDURE MyTests.[test1CleanUp]
AS
BEGIN
  --Undo anything that happened in MyTests.[test1]
  RAISERROR('TODO',16,10);
END;