Unit tests are over-hyped to the point people tend to ignore the other types of tests. A stored procedure and the C# code that uses it are different units, so a single unit test can not test them both. You'll need separate unit tests:

• Tests that mocks the stored procedure and verifies the C# code uses it correctly.
• Tests that activate the stored procedure the same way the C# code would activate it(if you really want to you can say it mocks the C# code) and verifies the stored procedure does what it is supposed to do.

Tests in both bullets(all unit tests actually) assume the interface between the C# code and the stored procedures remain the same. But if you plan on refactoring, there is a good chance you'll want to refactor that interface! Unit tests will not protect you then: if the interface changes both mocks are out-of-date and the tests will need to be rewritten!

I suggest that instead of focusing on unit tests you should focus more on integration tests. This means a single test can test both the C# code and the stored procedure it uses. This also means you don't need to mock the database(only the data), so writing the tests will be much easier.

See if you can use transactions around each test. BEGIN the transaction before you start the test, and after the test ROLLBACK the transaction. That way you won't have to repopulate the database for each test - every test in the session will use the same initial database and the framework will clean up after it.

A stored procedure and the C# code that uses it are different units, so a single unit test can not test them both. You'll need separate unit tests:

• Unit tests that mocks the stored procedure and verifies the C# code uses it correctly.
• Unit tests that activate the stored procedure the same way the C# code would activate it(if you really want to you can say it mocks the C# code) and verifies the stored procedure does what it is supposed to do.

Unit tests in both bullets assume the interface between the C# code and the stored procedures remain the same. But if you plan on refactoring, there is a good chance you'll want to refactor that interface! Unit tests will not protect you then: if the interface changes both mocks are out-of-date and the tests will need to be rewritten!

I suggest that instead of focusing on unit tests you should focus more on integration tests. This means a single test can test both the C# code and the stored procedure it uses. This also means you don't need to mock the database  (only the data), so writing the tests will be much easier.

See if you can use transactions around each test. BEGIN the transaction before you start the test, and after the test ROLLBACK the transaction. That way you won't have to repopulate the database for each test - every test in the session will use the same initial database and the framework will clean up after it.