This is the second in a series of articles on tSQLt:
Splitting into files and running tests
tSQLt organises test cases into test suites. A test case is a stored procedure whose name starts with the word test. A test suite is a schema that tSQLt knows about, and will normally contain one or more test cases.
Therefore, the top of a file of tSQLt tests will be something like this
USE myDatabase GO -- this creates the db schema and tells tSQLt that it contains tests EXEC tSQLt.NewTestClass 'MyTestSuite'; GO
You can run the following amounts of test via different tSQLt commands
|EXEC tSQLt.Run ‘myTestSchema.testA’||Run the single stored procedure (test case) called testA, in the schema (test suite) myTestSchema|
|EXEC tSQLt.Run ‘myTestSchema’||Run all stored procedures whose name starts with test in the schema myTestSchema|
|EXEC tSQLt.RunAll||Run all tests|
The output will show tests’ results sorted by test suite name, then by test case name. A test could pass, fail or give an error. An error is raised when the test can’t complete, e.g. you have a bug in your test code.
Structuring one file
Each test case must have its own stored procedure whose name starts with test, but it can be helped by other stored procedures if necessary. If the schema contains a stored procedure whose name is SetUp, this is run before each test case. This is good for set-up that is shared by all test cases without any variation – you can’t pass parameters to SetUp to tailor its behaviour per test case.
I often use one stored procedure to set things up for a test, and another stored procedure to execute the code under test, gather up the actual results and compare them to expected results. These are sometimes split up further into several stored procedures. The main stored procedure for a test case then calls the prepare stored procedure and the run-and-check stored procedure, passing in parameters specific to the particular test case.
CREATE PROCEDURE MyTestSchema.Prepare @thingOne INT, @thingTwo VARCHAR(20) AS BEGIN -- proper stuff here, mocking tables and stored procedures, setting up data etc. … END GO CREATE PROCEDURE MyTestSchema.RunAndCheck @thingThree BIT, @thinkFour INT AS BEGIN EXEC MyNormalSchema.ProcUnderTest @thingThree, @thingFour -- gather up the actual results … -- assert that they match expected results … END GO CREATE PROCEDURE MyTestSchema.[test case A] AS BEGIN EXEC MyTestSchema.Prepare @thingOne=17, @thingTwo=’alpha’ EXEC MyTestSchema.RunAndCheck @thingThree=0, @thingFour=88 END GO CREATE PROCEDURE MyTestSchema.[test case B] AS BEGIN EXEC MyTestSchema.Prepare @thingOne=942343, @thingTwo=’something big’ EXEC MyTestSchema.RunAndCheck @thingThree=1, @thingFour=3 END GO
The rest of this article will follow the standard structure of a unit test: Arrange, Act and Assert. This is what you would expect to find inside each stored procedure whose name starts with test, although this might be delegated to several stored procedures, as above. Hints for how to actually create a test using the elements described below are in the article Practical considerations.
This is where we get everything ready, which means:
- Mocking any tables the stored procedure under test needs for input or output
- Setting up data in those tables
- Mocking any stored procedures or functions that the stored procedure under test uses
- Creating tables as necessary for expected and actual results
- Preparing expected results
The background for mocking tables, stored procedures and functions is in the article Introduction to tSQLt.
Mocking a table
To mock the table mySchema.A you just do this
EXEC tSQLt.FakeTable ‘mySchema.A’
If you want to test the constraint called myConstraint (like a foreign key), add it to the clone of table A using
EXEC tSQLt.ApplyConstraint ‘mySchema.A’, ‘myConstraint’
If you want to test the trigger called myTrigger
EXEC tSQLt.ApplyTrigger ‘mySchema.A’, ‘myTrigger’
Setting up data in mocked tables
The mocked version of table A looks just like the real version of table A (apart from any foreign keys that you have chosen to omit). Therefore you can just insert into it as normal.
It’s worth adding in data that you expect to be missed by any WHERE clauses in the stored procedure under test. It’s tempting to write only data needed to support the expected behaviour, but I have found several bugs by adding in near-miss type input data. What a near miss means will depend on the WHERE clauses in question. The important thing is to make sure that the near miss data will affect the result, e.g. the wrong rows deleted, the wrong total produced etc.
Mocking stored procedures and functions
The simpler version of mocking a stored procedure, where the stored procedure being called is replaced by just an insert that logs the fact that the stored procedure would have been called, is:
EXEC tSQLt.SpyProcedure ‘mySchema.myProcedureName’
If you want to add some SQL that is added along with the logging insert statement, so it’s executed when the code under test is run, create the NVARCHAR variable @mySQLstring and then:
EXEC tSQLt.SpyProcedure ‘mySchema.myProcedureName’, @mySQLstring
To mock a function, by replacing the function mySchema.myFunctionName with the function myOtherSchema.myReplacementFunctionName, declare the function myOtherSchema.myReplacementFunctionName and then do:
EXEC tSQLt.FakeFunction ‘mySchema.myFunctionName’, ‘myOtherSchema.myReplacementFunctionName’
Creating tables as necessary for expected and actual results
This is just normal SQL Server table creation. It’s up to you whether you use normal tables (possibly in a schema that you use just for test stuff) or temporary tables.
Preparing expected results
This is more normal SQL stuff to put data in tables. You might be able to re-use the tables that you faked and put initial data in, but that depends on your code and tables. You might be able to make life easy for yourself here, by having a stored procedure that both sets up initial data in main tables and expected results.
This is the most straightforward bit of the test – you just execute the stored procedure under test, passing in parameters as appropriate for the test.
After the code under test has run, you might need to do some work to get actual results into the right form and place, or they might be fine as they are. If you need to do this, it’s just normal SQL Server selects and inserts. Once you have the actual results where they need to be you can check them against the expected results.
To check if two ints, strings etc. are the same, assuming that they are in the variables @expected and @actual:
EXEC tSQLt.AssertEquals @expected, @actual, ‘my message for when the test fails’
This seems to work with lots of different data types.
To compare two tables, e.g. mySchema.ExpectedValues and myOtherSchema.ActualValues:
EXEC tSQLt.AssertEqualsTable ‘mySchema.ExpectedValues’, ‘myOtherSchema.ActualValues’, ‘my message for when the test fails’
Rows in the two tables are matched column-by-column, so they don’t need to be in the same order. If there are any differences, you will see all rows in the output. Rows that have matched will be shown once, with an ‘=’ in an extra first column. Rows in only the expected results will have an ‘<’ in the extra first column, and rows in only the actual results will have an ‘>’ in the extra first column.
If you can’t easily compare two things, but instead do some other custom logic to see if the test has passed or failed, you can do the following as appropriate
EXEC tSQLt.AssertEquals 1, 1 EXEC tSQLt.Fail, ‘my message’
You can actually have up to 9 separate messages after tSQLt.Fail, which are concatenated in order.
An example of using Fail is below.
Example test 1 – a simple test
This is a simple example that shows how the various bits might come together.
CREATE PROCEDURE myTests.[test output is created for an odd input] AS BEGIN --- Arrange EXEC tSQLt.FakeTable ‘mySchema.myOutputTable’ DECLARE @oddValue INT = 17; -- creates and populates expected results table EXEC myTests.CreateExpectedOutput @oddValue -- Act EXEC mySchema.myStoredProc @inputParam=@oddValue -- Assert EXEC tSQLt.AssertEqualsTable ‘myTestSchema.ExpectedResults’, ‘mySchema.myOutputTable’, ‘output is created for an odd input’ END GO
Example test 2 – testing for exceptions
This is a silly procedure that you want to throw an exception for odd numbers but not even numbers, and a test (you would also want a test with an even number).
CREATE PROCEDURE myMainSchema.doNotLikeOddNumbers @numToTest INT AS BEGIN IF @numToTest % 2 = 1 THROW 200000, ‘I do not like odd numbers’, 1 END GO CREATE PROCEDURE myTestSchema.testOddNumber AS BEGIN BEGIN TRY EXEC myMainSchema.doNotLikeOddNumbers, @numToTest=3 EXEC tSQLt.Fail ‘there was no exception when there should have been’ END TRY BEGIN CATCH EXEC tSQLt.AssertEquals 1, 1 END CATCH END GO