Testing a stored procedure that operates on a caller's temp table

Question

I've been doing my best to follow the wisdom in http://www.sommarskog.se/share_data.html and the tSQLt documentation; trying to keep my stored procedures light and relatively uncomplex so that they are easily testable. So, I'm finding myself creating a temp table in a primary stored procedure, and then operating on that temp table in "secondary" stored procedures being called from the primary one. This works pretty well, but it's proving to be a little awkward to test.

When testing a "secondary" stored procedure in isolation, the temp table has to already exist. It looks like creating the temp table in a [Set Up] procedure doesn't persist to the unit tests, but creating a full table does.

So, to avoid repeating the CREATE TABLE #temp in every unit test (with its full column definition), I'm doing a variation of the following:

EXEC tSQLt.NewTestClass 'SEtest';
GO

CREATE PROCEDURE [SEtest].[SetUp]
AS
BEGIN
  CREATE TABLE SEtest.temptemplate (col1 int);
END;
GO

CREATE PROCEDURE [SEtest].[test example]
AS
BEGIN
  -- Assemble
  SELECT TOP (0) * INTO #temp FROM SEtest.temptemplate;
  INSERT INTO #temp (col1)
  VALUES (1),(2),(5),(7);

  -- Act
  EXEC dbo.REMOVE_EVEN_NUMBERS;

  -- Assert
  SELECT TOP (0) * INTO #expected FROM #temp;
  INSERT INTO #expected (col1)
  VALUES (1),(5),(7);

  EXEC tSQLt.AssertEqualsTable '#expected', '#temp';
END;
GO

Are there any better ways to reconcile tSQLt with sharing data between stored procedures via temp tables?

Answer 1

There is really no better way.

Using a #temp table as a table valued parameter reference is inherently ugly in T-SQL and leads to ugliness in the tests as well. However, your tests seems well thought out and should deliver what you are looking for.

The only improvement to your pattern I'd suggest is to create the SEtest.temptemplate table in the test schema permanently, instead of recreating it on the fly each time the test runs.

Using SELECT ... INTO to create test specific tables is a pattern I use all over the place in my tests. And as you cannot create temp tables in sub-procedures, it is the only clean option we have.

Either way, do not use a CREATE TABLE statement in your tests to create the #temp table as that would become a maintenance nightmare, should the table schema ever change.

---

Update:

There is one alternative that you might want to consider. As your #temp table is basically a table valued parameter, it wouldn't be a bad idea to use a table type. Now, that is still clumsy, but it increases maintainability on all ends:

DECLARE @template AS dbo.tabletype;
SELECT * INTO #temptable FROM @template;

That makes for a little clearer readability, particularly if the name of your table type is expressive (and it is in the same schema as the procedure in question).

With this you can use the same pattern in any calling procedure, not only on tests. That also makes the actual code a little more obvious/readable and it reduces maintenance work if the table definition should ever change.