Ok, so here’s the first in a series of a home grown implementation of Unit Testing SQL server code.
I’m not talking about using Visual Studio Team Edition for Professionals, like Jeff recommends… but I’ll try to provide a general approach to testing your SQL code, particularly stored procedures (sprocs).
In SQL processing, we have our classic CRUD operations, and those operations are impacted by combinations of existing data elsewhere in other columns, tables, databases, or servers. This is the single biggest challenge in the test as I see it.
Admittedly, I’m making the most of my current database architecture in this solution, and yet, I am having trouble imaging this not being a universal approach.
In my application to test, we are essentially a high-calculation, low-user interface SQL business engine. The calculations are all done in sprocs and they have been broken down into “blocks” of results. That is, Calculation B, will possibly take data results of Calculation A.
So here’s a basic diagram of this setup:
This approach allows a myriad of unit testing possibilities, but here’s my general approach.
Using NUnit, each Calculation would have it’s own TestFixture and the TestFixtureSetup would execute the sproc. The TestFixtureSetup would also retrieve any external data that the calculation would need into member variables for the fixture.
Then, each of the Test’s would look into any external data, WIP table(s) and final Calc table(s) to determine if it passed or not.
This is my first toe in the TDD pond for SQL applications.