I just completed a marathon coding session over the last week by logging 82.5 hours for the first phase of theÂ current project I am working on.Â I was developing code forÂ the COMPlusÂ business layer, the COMPlus data layer, and theÂ Oracle stored procedures for this phase of the project.Â The COMPlus layers will later be rewritten as .Net Enterprise Services (Serviced Components).
While writing the stored procedures, along with the COMPlus layers, I was sure to write unit tests in order to test assumptions and determine the best implementation of my interfaces.Â How did I write unit tests for stored procedures?Â First, I used the ad hoc way of creating several testProc.sql scripts that would exercise various options of my packages/procedures/functions and run those using the command-line SQL*Plus tool like so:
I could then batch these in a â€œsuiteâ€œ of scripts, for example testPackage.sql containing testProc1, testProc2, etc.Â By running the following:
I could then quickly test a set of procedures with this setup.Â If this sounds like a lot to keep track of, though, it is.Â But, at the time, it was something quick to use to test functionality and to test theÂ implementations of the interfaces.
Eventually, I remembered a unit testing tool that came out a couple of years ago:Â utPLSQL, originally written byÂ Steven Feuerstein (author of Oracle PL/SQL Programming, Third Edition).Â There is now an extension of these unit testing tools called OUnit.Â These tools are similar toÂ other xUnit tools (like NUnitÂ for .Net) based on Extreme Programming principles of test-firstÂ development.Â The utPLSQL and OUnit tools are a set of tables and packages/procedures that allow you to create setup/tear down procedures as well as test procedures.Â The ideas are the same:Â green lights (â€successâ€)Â are good, red lights (â€failureâ€) are bad.
Unfortunately, as far as I know, there is still nothing like this available for SQL Server.Â Â I have written my own set of unit test procedures for SQL Server (in Transact SQL, of course) when I was last doing heavy SQL Server development.Â I know that Ken Henderson talks about unit testing a great deal in his book The Guru's Guide to SQL Server Stored Procedures, XML, and HTML (an excellent book, by the way, on approaching SQL ServerÂ stored procedure development as a software process), butÂ there aren't many otherÂ resources out there.Â Â Who knows -- maybe I or someone else will write somethingÂ for SQL Server similar to what is available for Oracle.Â Â Â I am certainly interested in such a tool.