I've been looking into unit testing in SQL as a quick way to check out the effects of database changes.  I've been spending a fair amount of time working on SQL-only type changes - things that unit or functional tests won't catch either because we don't have a .Net API or front end application to test or because the types of things I need to check are data integrity stuff that would neccessarily make an application fail.

I originally started my search at the OpenSourceTesting.org site's listing of SQL testing tools.  Because I wanted to get up and running quickly and be able to run tests from any Dev, CI, or build machine, I immediately eliminated anything that had an external dependency or things like Python or Java.  Since I'm using MS SQL Server, that left me with just 2 options - SPUnit and TSQLTest.

The short version of my quick comparison is that I liked the simplicity and xUnit-like-ness of SPUnit, but TSQLTest had more advanced features and better output of test results.  For my project, we're going to start with SPUnit and implement some of the features of TSQLTest as we go along, aiming for a good hybrid of the two over time.

SPUnit is a project available on SourceForge by William Caputo who is a ThoughtWorks guy, which gave it a bit a credibility in my mind.  (TSQLTest )  Interestingly, there wasn't a download link on that page so I found the project page at http://sourceforge.net/projects/spunit.  Download and installation are simple, but documentation is a little bit sparse, mostly from a single cookbook page that shows a simple use.  Run the Create_SPUnit.sql script to create the SPUnit database and stored procedures. 

To create a test run using SPUnit:

• Create a test stored procedure in your database.  It's probably a good idea to prefix all your tests with "Test_" or something similar.  The test should contain a call to the SPUnit.dbo.Assert stored procedure with the actual and expected value parameters near the end.  It might look something like (tweaked from the cookbook):

CREATE PROCEDURE Test_UpdateOrder 

AS 

DECLARE @orderID int 
SET @orderID = 52 -- This order does not exist  

EXEC UpdateOrder @orderID, @newItem = 'foo' 

DECLARE @count int 
DECLARE @item varchar(50) 
    
SET @count = (SELECT count(*) FROM Orders WHERE orderID = @orderID) 
SET @item = (SELECT item FROM Orders WHERE orderID = @orderID) 

EXEC SPUnit.dbo.Assert @count, 1 
EXEC SPUnit.dbo.Assert @item, 'foo' 

GO

• Next create a suite, which is a store procedure called "Suite" in your database, which has a call to the SPUnit.dbo.AddTest stored procedure for each of the test procedures you want to run.  You can also specify setup and teardown scripts to get your test data in order; you may be able to share this with any sql you may have already created for NUnit.  Its like something like this (again from the cookbook):

CREATE PROCEDURE Suite 
@setup varchar(80) = Null OUTPUT, 
@teardown varchar(80) = Null OUTPUT 
AS 

EXEC SPUnit.dbo.AddTest 'SPSampleDB.dbo.Test_DeleteOrder' 
EXEC SPUnit.dbo.AddTest 'SPSampleDB.dbo.Test_UpdateOrder' 

SET @setup = 'SPSampleDB.dbo.TestSetup' 

GO

• To run your tests, just call the SPUnit.dbo.Run stored procedure, passing in the name of the database whose Suite you want to run.  It has output parameters for the numbers of tests and failures.

USE SPUnit 
GO 

DECLARE @testCount int 
DECLARE @failureCount int 
DECLARE @passCount int 

EXEC Run 'SPSampleDB', @testCount OUTPUT, @failureCount OUTPUT 
    
SET @passCount = @testCount - @failureCount 

PRINT 'run: ' + CONVERT( varchar(4), @testCount ) + ' passed: ' + CONVERT(varchar(4), @passCount ) + ' failed: ' + CONVERT( varchar(4), @failureCount ) 

• The output looks like this:

testName 				asserted 
-------------------------------------------------- 	---------------------- 
SPSampleDB.dbo.Test_UpdateOrder 	Actual: 0 Expected: 1 

In case you were wondering, here's some key additions I've identified:

• Run this via NAnt.  Not a big deal since sqlcmd is a piece of cake.
• Spit out an NUnit style XML document.  Again, not a big deal and TSQLTest does this out of the box.
• Multiple Suites within a single database.  I like the idea of having an orders suite and a customers suite, etc.
• The ability to run a single test standalone.  Mostly for development and debugging purposes.
• More types of Asserts.  It would be nice to have some wrappers for checking column order, data type, etc.

That's it.  You can be up and running in like an hour, depending on how many tests you start with.

PS: I apologize for any code mutations.  I'm having a bit of trouble formatting code with Windows Live Writer > Community Server.