Unit tests for Stored Procedures in SQL Server

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?

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/

Welcome to tSQLt, the open source database unit testing framework for SQL Server

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)

It is doable. Create tests and in the setup create a new instance of db and give it some data and then execute the procs. Validate your assumptions, like I got the correct data back. Drop the test db then do it all again in the next test.

.
.
.
.