I've been working on the whole concept of unit testing TSQL for quite a while. At this point, I'm a bit more in Phil's camp. We do "unit testing" as a matter of course, but getting into this formalized, structuralized methodology just hasn't paid off a lot in terms of benefits. One thing you can do to make your life easier though, is to automate, as much as you can, the generation of the unit tests. I wrote up on TSQL Unit a while back here on SQL Server Central[/url].

We've actually discovered that the number of unit tests we need has dropped radically because of a few things. First, we moved most of our projects into Microsoft's Visual Studio Team System Database Edition. The instant syntax checking and the ability to test our deployments ahead of time eliminates most of the simple database errors that unit testing is designed to discover. Then, we've found that most of the problem isn't that the database is not returning data as defined by a test, but that it isn't returning data as defined by the program. The fact is, if I take our requirements and write a proc to return them appropriately, that doesn't mean that's what the app actually needs. We still have to go through a round of testing with integration with the app and the unit tests don't help. Finally, we're getting a push towards using Object Relational Mapping software, in our case nHibernate. This means that there won't be a proc at all, therefore no need for unit testing on the database.

Also, the tests you outlined don't test result sets. You're still checking for a single value, not the entire set. We worked out a method of using XML & storing it locally to get a full record set evaluation. Unfortunately, it's a ton of work to maintain. The benefits have yet to outweigh the costs.