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:
@testProc1
I could then batch these in a “suite“ of scripts, for example testPackage.sql containing testProc1, testProc2, etc. By running the following:
@testPackage
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.
