Last week I attended another ILP workshop, this time about nUnit. This made me remember about the TSQLUnit testing framework, and the changes I had made to it whilst using it at my previous company and the fact that these changes were just sitting in a file somewhere and had never been shared.

The project which benefited from the most from TSQLUnit was one working with a large set of complicated calculations. Due to the nature of the data, this work was carried out in the database layer. These calculations were based on a well specified system, and so was a great candidate for thorough unit testing. Whilst building the calculation engine, I enhanced the base TSQLUnit installation to have some additional helper functions to save me coding the same thing over and over again.

I added a set of value checking helper procedures as follows
tsu_CheckValues_Decimal, tsu_CheckValues_Int, tsu_CheckValues_String, tsu_CheckValues_Date, tsu_CheckValues_Money all of which take 3 parameters - the first the expected data value, the second the actual data value, and the third an optional parameter expressing the message to be displayed to the developer if this assertion fails.

For example, EXEC tsu_CheckValues_Int 0,1,'Oops'
 results in the following error being recorded 'Integer value mismatch Oops - Unexpected Result. Expected: 0. Actual: 1'

In additon I added some helper functions to assist in the generation of error messages as follows
tsu_GenerateErrorMessage_Date, tsu_GenerateErrorMessage_Int, tsu_GenerateErrorMessage_Real, tsu_GenerateErrorMessage_String all of which take 3 parameters, the first being the initial part of the message to display, the second the expected data value and the final the actual data value.

For example, SELECT dbo.tsu_GenerateErrorMessage_Int('Integer value mismatch',0,1) which results in the following text being generated 'Integer value mismatch - Unexpected Result. Expected: 0. Actual: 1'

The helper functions do not alter or change the basis under which TSQLUnit operates, and so they should install and work happily within existing TSQLUnit installations. Download the TSQLUnit Helper Functions.

I have also provided a script containing sample TSQLUnit tests - one coded to pass, one coded to fail, both making use of the tsu_CheckValues_Int.

In another post I will go into the other changes I made, and the recommendations for testing we came up with as a result of this exercise.