I have had many occasions recently where I have needed to maintain complex stored procedures and functions. These were broken already, usually in fairly subtle ways – there were very few occasions where you would call the SP with valid parameters and it just plain didn't work.
My solution was to evolve a framwork that executes the stored procedures inside a transaction, after initializing the database to the initial conditions I needed, then testing for the expected result, also within the same transaction. The transaction was rolled back at the end of the test.
This has worked very well. But some would call this "integration testing" since it involves integrating with the database. I call it unit testing, since I tested individual components and individual test cases for those components, and since I completely controlled the initial state of the database.
But where should the line be drawn? Is this integration testing or unit testing? Is there a practical reason why this sort of test is a bad idea? If this is "only" integration testing, does anyone have suggestions on how to do actual "unit tests" on these stored procedures?
Update, 3 1/2 years later. On my current project, I have begun using SSDT unit tests, with success, though they could be better. See Verifying Database Code by Using SQL Server Unit Tests. These typically deploy the database project to your instance of SQL Server LocalDB, so this removes any question about the database environment affecting the test. I populate the database with the required data during the Pre-Test, which removes questions about the database contents. In fact, I use MERGE statements to do this, ensuring that any data I don't need for the current test is removed, inserted or updated from the database before the test. They do have issues:
- They are not fast
- It is not possible to reuse test conditions
- It is not possible to reuse pre-tests (unless you make them common to all tests in a project)
- The user interface could be improved
One of the reasons for the above issues is that I have not yet complained about them. I recommend that anyone interested should try this feature, and then complain about it. That's how improvements are made.
Best Answer
The point of unit testing isn't to make a magic unit test fairy come and validate your opinion. It's to test the smallest, simplest building blocks of your system, so you're not testing some more extensive functionality and tripped up because something didn't work the way you thought it did. So, can you break this down further? I don't think you can, in which case:
(a) It sounds like a unit test to me, and (b) It doesn't matter whether it's a unit test or not, because it tests what you need testing, which is the point of using unit tests in the first place!
If you feel the procedures are too complex, you may well want to break them themselves into smaller parts (in database procedures or in code), but to do that you would obviously like to have these tests in place first!