Up to now the previous blogs on unit testing sprocs have focused on those that output data – ‘Select’ sprocs. Over the next few weeks I intend to write a series of blogs that will look at testing sprocs that carry out other types of data manipulation.
The steps outlined in Database unit testing patterns will be followed for testing delete, insert and update sprocs. But as steps 1,2 and 4 are effectively the same as outlined previously, the focus will be on Step 3 – testing the functionality of the sproc.
Does the sproc delete/insert/update as expected?
Over the series a functional test pattern will emerge for testing all three types of sprocs.
This can be outlined as:
1. Test initial state is as expected
For example, if testing an update sproc ensure that the row(s) to be updated has the expected initial values (ie it hasn’t already been updated by a previous test and not ‘rolled back’).
2. Run the sproc
3. Test post state is as expected
For example, after running an update sproc ensure that the row(s) has been updated correctly AND it hasn’t inadvertantly effected any other rows.
I will give examples of two different approaches that can be used to do the steps outlined above:
A. Using the ExportDBDataAsXML tool to create expected pre and post state XML files that can that can be compared to ‘actuals’.
B. Using ‘ad hoc’ SQL commands to test checksums, row counts and values in rows for both pre and post states.
The series will start with a relatively straight forward example of an update sproc – taken from Microsoft’s AdventureWorks2008R2 – which updates a single table. This will then be extended to look at how to handle more complex scenarios, such as where 2 or more base tables are involved.
DBTestUnit 