What is the best way to test a stored procedure?

A colleague swears by the TSQLUnit testing framework. May be worth a look for your needs.

Not sure if this is what you're looking for, but since you're using SQL Server: I've found LINQ to be a great tool test stored procs. You can just drag the stored procedures onto a DBML diagram and then call them as methods on your datacontext. Beats setting up ADO connections etc for a test harness. If you set up a test project in Visual Studio for example, you can simply test your procedures like methods on another object. If your stored procs return result sets, I think LINQ will translate that into anonymous variables that you should be able to access via IEnumerable or IQueryable (somebody pls verify this). But if you're returning return codes only, this should be a quick and fairly easy way.

We had a very thin Data Access layer which basically facaded stored procedures to look like C# methods. Our NUnit test-suite then had SetUp/TearDown to create/rollback a transaction and test methods that called into DAL. Nothing fancy, and proved to be easier to maintain than TSQLUnit test-suite.