harriyott.com

Unit testing stored procedures

Part of our development standards is to use TSQLUnit for unit testing our stored procedures. Over the last couple of days I've been adding this to my daily build process. We have a database maintenance utility which I have modified to run unattended, so the build machine can create its own database as part of the build.

I've added a stored procedure to the TSQLUnit framework which essentially runs all the tests by calling tsu_runTests. The results are written by default into a table, with success being marked as 1 in the [success] column, and failure being 0. Selecting 1 - [success] in the stored procedure will give a 0 for success and a 1 for failure. MSBuild will recognise these values, causing the build to pass or fail if the tests pass or fail.

To run the unit tests in the build, I'm using osql in an exec task. From memory, the osql command is something like:
osql -E -Q "EXIT(EXEC tsu_runTestsReturningResult)"
The build machine now runs the unit tests, and the build fails if a single test fails, and will only move on to the next step if they all pass.
17 May 2005