Testing Stored Procedures at times can get very difficult. I am sure there are several frameworks and tools available out there. But, I wanted to use NUnit and came across this excellent article by Alex Kuznetsov and Alex Styler. Their framework lets you compare results sets and even entire database modification changes. And as expected, rolls back after the tests, so the db is maintained in a clean state.
But, I wanted some initial test data to be populated in the database and at times the initial data need to be different for different tests. To solve that issue, I used NDbUnit, which helped me to put my database in a known state and thereby increasing repeatability in my unit tests.
Using NDbUnit and Alex's Database Testing framework, I was able to build stable and consistent unit tests to test the database table, stored procedures.
If someone would, like to see an example, please let me know and I could send it to you.