Test Driven Development with SQL Server Databases

Test driven development is easy when you're programming in a language such as C# or Java. Tools such as NUnit are available to help you write tests that can be run regularly and that ensure that changes you make to your application do not break other things.

However, when much of your business logic is in the form of SQL Server 2000 stored procedures, unit testing can be more problematical, and testing tends to be done on a more ad-hoc basis. Subtle changes in table or view structures can break things very easily.

To simplify testing, and allow Test Driven Development, where tests are written first, I propose creating a test stored procedure for each real stored procedure as follows:

1) Begin a transaction
2) Set up any initial data
3) Run the stored procedure
4) Verify the data in the database
5) Display a success or failure message
6) Rollback the transaction
7) Return a value for success or failure

It'll be easy to write a GUI front end to run all the test procedures and display red or green bars according to which tests pass according to their return value. As tests are all individual transactions that are rolled back whether they succeed or fail, the database will end up in the same state as it was when it started.