Search code examples
sqlsql-serverunit-testingstored-procedurestest-first

Unit tests for Stored Procedures in SQL Server

I want to implement Test First Development in a project that will be implemented only using stored procedures and function in SQL Server.

There is a way to simplify the implementation of unit tests for the stored procedures and functions? If not, what is the best strategic to create those unit tests?

Solution

  • It's certainly possible to do xUnit style SQL unit testing and TDD for database development - I've been doing it that way for the last 4 years. There are a number of popular T-SQL based test frameworks, such as tsqlunit. Red Gate also have a product in this area that I've briefly looked at.

    Then of course you have the option to write your tests in another language, such as C#, and use NUnit to invoke them, but that's entering the realm of integration rather than unit tests and are better for validating the interaction between your back-end and your SQL public interface.

    http://sourceforge.net/apps/trac/tsqlunit/

    http://tsqlt.org/

    Perhaps I can be so bold as to point you towards the manual for my own free (100% T-SQL) SQL Server unit testing framework - SS-Unit - as that provides some idea of how you can write unit tests, even if you don't intend on using it:-

    http://www.chrisoldwood.com/sql.htm

    http://www.chrisoldwood.com/sql/ss-unit/manual/SS-Unit.html

    I also gave a presentation to the ACCU a few years ago on how to unit test T-SQL code, and the slides for that are also available with some examples of how you can write unit tests either before or after.

    http://www.chrisoldwood.com/articles.htm

    Here is a blog post based around my database TDD talk at the ACCU conference a couple of years ago that collates a few relevant posts (all mine, sadly) around this way of developing a database API.

    http://chrisoldwood.blogspot.co.uk/2012/05/my-accu-conference-session-database.html

    (That seems like a fairly gratuitous amount of navel gazing. It's not meant to be, it's just that I have a number of links to bits and pieces that I think are relevant. I'll happily delete the answer if it violates the SO rules)