I was reading a post on Rob Caron's blog where he links to a few posts by Sachin Rekhi (a program manager from the Data Dude team).
There are a number of posts about how the new database unit testing features work, and he walks you through implementing a database unit test. My opinion is that the database unit testing functionality isn't very valuable as it stands today. It appears targetted to allow you to test a very limited set of conditions: the row count, a scalar value returned, a empty/non-empty result set, and/or the execution time.
I don't know about the rest of you guys, but if I'm writing unit tests against my stored procedure, testing that the row count equals what I expect is not nearly good enough to ensure that the stored proc is doing what I expect. An example Sachin gives is testing a [Ten Most Expensive Products] stored procedure, and he checks to see that the row count equals 10. If I was writing a unit test against a Ten Most Expensive Products stored procedure, the most important thing to test for me would be that the 10 products being returned actually match the 10 most expensive products in the database. I would test this by having some code to initialize the database with a known set of sample data, then execute the stored proc testing to make sure that the 10 product id's returned are what I expected.
Don't get me wrong I like the idea of writing unit tests against stored procedures. In fact I do this today in some cases. However, I do it through code, so that I can actually iterate through the resultset and check the data using assert statements. If I could do this testing at the SQL level and eliminate the data access layer from my test, then all the better. But I really think that there are more tools needed in the database unit testing feature-set to really deliver on the value proposition.