tSQLt: share data between SetUp and tests

Question

I am using tSQLt to unit test t-sql code.

Quite often the Arrange part of tests is quite extensive and I am trying to push a lot of it out to the SetUp procedure to reuse among the tests within the class.

It would be very useful if Setup and test procedures could "know" the same information, i.e. have some shared data. For example, let's say setup creates a test invoice and sets invoice ID to something known:

CREATE PROCEDURE [InvoiceManager].[SetUp]
AS
  DECLARE @TestId INT = 10;

  EXEC tsqlt.FakeTable @SchemaName='dbo', @TableName='Invoice';
  INSERT INTO dbo.Invoice (Id, Amount) VALUES (@TestId, 20.50);
GO

And then in the test we want to do something to the test invoice, like so:

CREATE PROCEDURE [InvoiceManager].[Test_InvoiceHandler]
AS
  DECLARE @TestId INT = 10; -- duplication I would like to eliminate

  -- Action
  EXEC dbo.InvoiceHandler @InvoiceId = @TestId;

  -- Assert
  -- ... some assertions
GO

Would be nice to be able to replace duplicating the @TestId's value in both (and more) procedures by just pushing it into some "class variable" in SetUp procedure and then use it from the tests. Any ideas how to achieve it in a compact manner? I could imagine creating a table in [InvoiceManager] schema and reading from it in tests. Any chance something like this exists just that I can't find it in the docu? Thanks!

Answer 1

One approach would be to change how you do your setup. Instead of defining the 'Arrange' data in the SetUp procedure, you could create a new procedure on your test schema. For example, InvoiceManager.Arrange. This procedure could take your @TestId as an input parameter. You would then call InvoiceManager.Arrange from each of your test procedures. I use this technique quite often instead of using SetUp and find it works quite well. Even though I need to explicitly call it from each test procedure, I find that I can break up my Arrange step into multiple, well-named stored procedures if it is complex.

Here's an example to illustrate how I would solve your question:

CREATE PROCEDURE [InvoiceManager].[Arrange]
  @TestId INT
AS
  EXEC tsqlt.FakeTable @SchemaName='dbo', @TableName='Invoice';
  INSERT INTO dbo.Invoice (Id, Amount) VALUES (@TestId, 20.50);
GO

CREATE PROCEDURE [InvoiceManager].[Test_InvoiceHandler]
AS
  DECLARE @TestId INT = 10;
  EXEC InvoiceManager.Arrange @TestId;

  -- Action
  EXEC dbo.InvoiceHandler @InvoiceId = @TestId;

  -- Assert
  -- ... some assertions
GO