Alex Rivera | Logout

Has anyone had any success in unit testing SQL stored procedures?

Asked 2008-08-15T15:29:14.837
37

We’ve found that the unit tests we’ve written for our C#/C++ code have really paid off. But we still have thousands of lines of business logic in stored procedures, which only really get tested in anger when our product is rolled out to a large number of users.

What makes this worse is that some of these stored procedures end up being very long, because of the performance hit when passing temporary tables between SPs. This has prevented us from refactoring to make the code simpler.

We have made several attempts at building unit tests around some of our key stored procedures (primarily testing the performance), but have found that setting up the test data for these tests is really hard. For example, we end up copying around test databases. In addition to this, the tests end up being really sensitive to change, and even the smallest change to a stored proc. or table requires a large amount of changes to the tests. So after many builds breaking due to these database tests failing intermittently, we’ve just had to pull them out of the build process.

So, the main part of my questions is: has anyone ever successfully written unit tests for their stored procedures?

The second part of my questions is whether unit testing would be/is easier with linq?

I was thinking that rather than having to set up tables of test data, you could simply create a collection of test objects, and test your linq code in a “linq to objects” situation? (I am a totally new to linq so don’t know if this would even work at all)

sql unit-testing linq-to-sql
Edit
Report

2 Answers

6

If you think about the kind of code that unit testing tends to promote: small highly-cohesive and lowly-coupled routines, then you should pretty much be able to see where at least part of the problem might be.

In my cynical world, stored procedures are part of the RDBMS world's long-standing attempt to persuade you to move your business processing into the database, which makes sense when you consider that server license costs tend to be related to things like processor count. The more stuff you run inside your database, the more they make from you.

But I get the impression you're actually more concerned with performance, which isn't really the preserve of unit testing at all. Unit tests are supposed to be fairly atomic and are intended to check behaviour rather than performance. And in that case you're almost certainly going to need production-class loads in order to check query plans.

I think you need a different class of testing environment. I'd suggest a copy of production as the simplest, assuming security isn't an issue. Then for each candidate release, you start with the previous version, migrate using your release procedures (which will give those a good testing as a side-effect) and run your timings.

Something like that.

answered 2008-08-15T15:51:15.433
0

LINQ will simplify this only if you remove the logic from your stored procedures and reimplement it as linq queries. Which would be much more robust and easier to test, definitely. However, it sounds like your requirements would preclude this.

TL;DR: Your design has issues.

answered 2008-08-15T15:44:00.127

Your Answer