If someone is intrested how I'm testing stored procedures I want to share.

I have Visual Studio with Resharper.

I create test dll project in c# that is using NUnit as a test framework.

Then I write a tests that

1. Delete test database

2. Create new one

3. Run setup scripts twice - to be sure that it will work correctly also on already installed DB

4. Run each test in a way that all changes done by it are removed after test

The longest part is to write support classes/methods that allow you rollback all your changes from DB after each test (point 4.).

I'm not using transactions for it, because I have many places where I'm testing code that cannot be run under transaction (like db user creation).

In each test I prepare data in database then I run some SQL code and I'm checking results.