Unit Test Stored Procedures? Here's one example...

Gunnar Peipman's ASP.NET blog - Using database unit tests in Visual Studio

Stored procedures in SQL Server database can be unit tested using Visual Studio database unit tests. It may seem as simple thing to do at first but when you start writing tests then you will find out it’s more complex task to do then expected. In this posting I will introduce database testing with Visual Studio tools.

NB! In this posting I will use Visual Studio 2010 as I was not able to make database unit tests run on Visual Studio 2012. VS2012 has also these tests available but it seems to me that this support is still raw and waits for stabilization and fixes.

Are they actually unit tests?

I am not very sure that these tests can be called unit tests. They are different than unit tests we are used from applications development. As these tests may also test integrated components in database I would rather call these tests as database tests. Considering these tests to be wider than unit tests is okay because same mechanism allows us write very different tests for databases.

Why database testing?

Databases are often more complex beasts than just some tables, keys and indexes. Often databases contain complex queries, stored procedures and user-defined functions. SQL Server has also support for CLR libraries. All these database objects contain some logic that is usually very important.

Systems that make heavy use of stored procedures are good targets for database tests. Actually there are two ways how to test these databases:

• regular integration tests against some service or set of classes that make data available to system,
• database tests.

Database tests seems better option to me because then we don’t include code from other layers to tests and therefore bugs in other layers cannot affect the results of database tests.

..."

Every six months I get this "want" to unit test my SQL and go about re-discovering what the current state of it is. This post reminded me that it's getting to be close to that six month period...