2

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?

1
  • Can you share more detail on what the test should do? This might be a XY Problem
    – Zikato
    Jun 20 at 9:02

1 Answer 1

7

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;
3
  • I wish I could give your answer more votes . Thank you @Sebastian Meine Jun 21 at 6:56
  • I was very happy when I read the answer and the document in tSQLt site. But as I tested the query it does not work. I use the sample code in your answer but still @@TRANCOUNT returns 1. Also the clean up procedure did not execute. Jun 21 at 9:46
  • @MeyssamToluie, make sure that you are using the latest version of tSQLt. This is a brand new feature. Also make sure that you have the annotation exactly as in the example above as a single line directly in front of the CREATE PROCEDURE. If that still does not work, show us your code, please, potentially in a new question. Jun 21 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.