5

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
miguelbgouveia
  • 2,963
  • 6
  • 29
  • 48

3 Answers3

5

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)

Chris Oldwood
  • 1,060
  • 10
  • 17
  • 1
    Your post was very useful for me, thanks. I’m starting exploring your Unit Test Framework and for now seem very good for my intent. I just find a possible issue in the installation scripts. The file CreateSchemas.sql doesn’t create the ‘test’ schema. If there is a better places to post this issue let me know. Just a final suggestion, you should put your code in an open source repository like github, it will be more accessible and you can benefit for further improvements for voluntary people. – miguelbgouveia Jul 09 '13 at 11:00
1

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.

Blair Davidson
  • 901
  • 12
  • 35
1

Unit testing in database is actually big topic,and there is a lot of different ways to do it.I The simplest way of doing it is to write you own test like this:

BEGIN TRY
<statement to test>
THROW 50000,'No error raised',16;
END TRY
BEGIN CATCH
if ERROR_MESSAGE() not like '%<constraint being violated>%'
THROW 50000,'<Description of Operation> Failed',16;
END CATCH

In this way you can implement different kind of data tests: - CHECK constraint,foreign key constraint tests,uniqueness tests and so on...

dixpac
  • 533
  • 2
  • 10
  • Is this a good unit test? If you are testing a stored procedure, this would be checking to see if it exists and that it runs, but wouldn't be checking if it does the right thing (ie: that it's behavior is that which is relied upon elsewhere). – Maverick Nov 12 '14 at 02:59