Testing stored procedures' logic

Question

We have quite a bit of logic in TSQL stored procedures. Being a big fan of automated tests, I started writing automated tests for stored procedures.

I test stored procedures by calling them from a C# project. I make all tests touching the database inherit from a base class the ensures the test is run in a TransactionScope that is never committed. There are several issues related to this:
- The tests are slow compared to tests not touching the database but not much can be done about that I suppose.
- Sometimes I run into FK issues when I want to get the database into the state I need it to be before the test logic is run. E.g. when I need to truncate a table that is referenced by FK you can't do that. I need to get rid of the rows in the referencing table as well.
- Sometimes I need to write a new stored procedure or query to get the database into the state that you need it in. On the other hand I ofter find out I need a similar procedure later for new functionality anyway.

Despite these downsides, I still choose to write tests for stored procedure logic as we have quite a bit of logic in stored procedures. Do you use a similar approach, something completely different (like TSqlUnit) or you don't bother testing sprocs?

Answer 1

I think there is benefit to testing your stored procs from code as it keeps all the tests in one place where they can be easily run on the dev machine or on a build machine.

Though it goes against Unit Testing principles (not setting up and tearing down for each test) I use a copy of the database exclusively for testing and a script that sets the data up in the way that I need. This script can be rerun as necessary for a clean test database. The downside to this is continually updating the script, as you mention foreign keys are a pain. I use a SQL Server Management Studio plugin to script the data inserts to simplify this process.

I believe this is the plug-in that I use: http://sqlblogcasts.com/blogs/seanprice/archive/2007/08/28/data-scripter-add-in-for-management-studio.aspx