How to run the same tsqlt tests on multiple stored procedures?

I have lots of stored procedures, and each stored procedure has its own tsqlt test class. In each test class, roughly 5 of the tests are exactly the same for all the stored procedures, and roughly 5 of the tests are unique to the stored procedure.

Occasionally I want to change one of the "common" tests, and I have to change it in 10 or more files, which is a nuisance.

Is there some way that I can define a group of tests in a single file, and then call these tests from another test class, so that the tests are run on the stored procedure that is being tested by the calling test class?

Solution

One solution might be to create a TestHelpers class, add your common test code to this class but without the "test" prefix so tSQLt doesn't run them automatically. These procedures would need input parameters such as the name of the procedure to test etc. and would also include the standard tSQLt assertions.

Then within your procedure-specific test classes you would just call the TestHelper version from within the test.

I'm not sure of your exact use case but let us assume that one of the common factors between these procedures is that they all return the same result set (but with different contents) and you want a common test to assert that the result set structure is as it should be.

You might create a helper procedure like this

create procedure [TestHelpers].[ValidateResultSet]
(
  @ProcedureToTest nvarchar(200)
)
as
begin
    --! common set up omitted for brevity
    exec tSQLt.AssertResultSetsHaveSameMetaData...
end

Then in your procedure test classes you might create tests that look like this:

create procedure [MyFirstProcedureTests].[test result set]
as
begin
    --! MyFirstProcedure-specific  set up omitted
    exec TestHelpers.ValidateResultSet 'MyFirstProcedure';
end

create procedure [MySecondProcedureTests].[test result set]
as
begin
    --! MySecondProcedure-specific  set up omitted
    exec TestHelpers.ValidateResultSet 'MySecondProcedure';
end

I don't have access to a database right now to prove this but it should work as I've done similar things in the past.