I've used both (and wrote articles on both). I think the TSQLUnit is a more pure version of unit testing than DBPro. TSQLUnit also has pretty good reporting capabilities on the results of multiple tests.

DBPro is a lot easier to use and more easily extended than TSQLUnit. Then there's the convenience factor. As someone noted above, right click on a proc and you've got a unit test with parameters built in. Too easy.

I don't know that we'll write formal unit tests for all our procs, but we know that some apps or some procs within apps are candidates for frequent tweaks during development. So, those we get tests for. They've proved invaluable. I just recently rewrote a series of procs from an old project. I created tests for procedure with a before & after run through the data compare extension. I was able to validate these procedures as I went which convinced the business unit that I wasn't ruining their lives but rather improving them.