Transact Sql Unit Testing

Unit testing is awesome.  It has become my new software development passion.  Where once I tried to live without it, now I try to dream up tests I haven't thought of, just so I can write more tests (geeky, I know).  All of our C# libraries are unit tested, down to a very, very fine detail, and it is incredible how much cleaner our application is before each release.

Which led me to the idea:  why can't I unit test my sql scripts as well?  Seems a logical extension, right?  I write a lot of sql procs / scripts for updating data.  Why shouldn't I unit test these changes as well?

So I looked around, and while I found a few frameworks, and a lot of advice, I didn't find anything that would work particularly well in our space.  The thing was, the frameworks were all written in different languages, like Java or Python.  The advice was mostly around commonsense things to do, like after running an update, run another query to ensure the data was there.

What I wanted was a simple, lightweight set of unit testing tools to help me as I coded my SQL.  These would be called to verify my changes either immediately after I updated the data, or in a separate set of scripts.  Just like unit tests, right?

So what I came up with was a simple set of Assert stored procedures.

Assert_IsTrue

Assert_IsFalse

Assert_AreEqual

Assert_AreNotEqual

Assert_Fail

Assert_IsNull

Assert_IsNotNull

Each of these, like their java / C# / ruby counterparts, allow me to check a given bit of change, and report back if it failed.

As an example, let me show you a simple call to Assert_IsTrue:


declare
@Result int
,@ErrorMessage nvarchar(1000)

select @Result = COUNT(1) from etBillingTaxRate where TaxRateAmount = 153.0

set @ErrorMessage = 'Could not find a billing tax rate of 153.00'

exec dbo.Assert_IsTrue @Result, @ErrorMessage




As you can see, the Assert_IsTrue takes 2 parameters: an integer and an nvarchar.  The nvarchar is obviously the message to return if the assert fails.  For  Assert_IsTrue and Assert_IsFalse, I used an integer (as Transact SQL has no boolean data type).  If this integer is 1 or greater, the tests passes.  If the integer is 0 or less, we get the following when we run the script:






Msg 50000, Level 15, State 1, Procedure Assert_IsTrue, Line 19
Could not find a find a billing tax rate of 153.00

You can download my assert stored procs from here TSQL_UnitTestingProcs.sql, and use them in your own Transact SQL projects.  For developers of other databases, it would be very easy to port these to your language.  They are ridiculously simple, but make your sql coding life so, so much easier.