I looked at tsqlunit. With VS2005, I think it is easier to use NUnit to test our stored procedures. Here's what we've done (VB.NET code)...
1) Created an NUnit test fixture that utilizes SWC (Services Without Components) and transactions. This works for us because all of our development PCs are on Windows XP. Here's the basic transaction class.
Public MustInherit Class TransactionFixture
Private tx As CommittableTransaction
Public Sub Setup()
tx = New CommittableTransaction
' Make the transaction the ambient transaction.
' ADO.NET and COM+ will automatically participate
' with this ambient transaction.
Transaction.Current = tx
Public Sub TearDown()
2) All test cases that use the database inherit from TransactionFixture. This provides automatic rollback capability.
3) It's still a lot of work to set up test data. But we don't have to worry about cleaning it up because all the work is automatically rolled back.
4) Refactor, refactor, refactor! The same Insert statement may be useful in 20 different tests. Don't copy it. Centralize it in a utility module.
Our inspiration came from "Test-Driven Development in Microsoft.NET", by James Newkirk and Alexei Vorontsov. Additional inspiration came from the many bloggers who wrote about the amazing new features in Visual Studio 2005.