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.

  • 1
    Why would it be an oxymoron? Commented Jun 6, 2011 at 6:08
  • Good question. What do you think of changing the title? Perhaps something like this: "How to unit test stored procedures" Commented Jun 6, 2011 at 11:23
  • @Matthew: "collaboratively-edited" Commented Jun 6, 2011 at 12:29

6 Answers 6


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!


Unit testing by definition revolves around testing a "unit" of code in it's running environment or platform. The platform your using is a database so you have to use it, it's not integrative.

A better question is why do you care. As long as it's automated and adds value do care that your test in a unit test, an acceptance test, a smoke test or a functional test?

At my work we currently leverage the unit testing framework to do Acceptance Test Driven Development (ATDD). Some tests are integrative, some are not. Almost none of them are unit tests, but we don't care as long as:

  • the test adds value
  • the test doesn't have false failures or successes (is mostly deterministic)
  • the test is automated


It's all a question of cost and benefit. The more moving parts you have the higher the risk of having a non-deterministic test. Classic unit tests have the least amount of moving parts, and therefore the least chance of being non-deterministic. The tradeoff is you aren't testing the integration points (code, database, fs, network, etc). All of these are useful to have covered in a test, as long as the risk of false failure or success is low enough.

Tests provide value in what they do not what they are. The key is how often you are going to have false failures and success. If I have false failures for 2 hours every month because that is maintenance window for the network, then the value of the tests are apparent. When they fail it's obvious there is something wrong with the networked resource, not a particular test. And now you have more test coverage precisely because you are testing those integration points.

  • One of the reasons I care is that some places will complain if your unit tests aren't "unit tests" by their definition. Another reason is that it came up in comments on "Does a Middle Ground Exist? (Unit Testing vs. Integration Testing)" Commented Jun 6, 2011 at 12:33
  • @John Based on @dietbuddha's answer and the research I've read: Because you are utilizing the network stack and the DB server, your tests are not "mostly deterministic." They should not be classified as unit tests. But of course that's just my opinion. Commented Jun 6, 2011 at 13:17
  • 1
    @Matthew: I don't know what platforms you develop on, but I guarantee you that the network stack and database server I use don't fail very often. In fact, failures of these components can be ignored for the purpose of testing. Commented Jun 6, 2011 at 13:20
  • @John Saunders: updated answer
    – snakehiss
    Commented Jun 6, 2011 at 15:22

I think it depends where the data comes from. If you're creating your test conditions in the test prefix I think it's reasonable to call it a unit test. Otherwise you're into nit-picky arguments about what counts as "pre-existing" for a unit test. Just as your database unit tests rely on the existance of database tables and so on, unit tests outside the database rely on things like class definitions and often instances of those clssses. That's not bad, it's realistic.


I would consider that to be integration testing:

  1. It's much slower than a unit test should be.
  2. It does not exercise a single, atomic unit of code -- it exercises your procedure, the database server, the network stack, etc.
  3. It won't be easy to detect where the error occurred if the test fails. You'll have to look at each failure manually to see if it was an external system failure, or your code.

However, tests like you describe are invaluable to have. We recently started adding tests just like that, to test stored procedures that are impossible to test manually in certain environments. I don't know another way to have automated tests for stored procedures.

So, these tests are a great idea, but call them integration tests -- use a test category or name suffix to distinguish them from regular unit tests. This way, you can flag integration test failures differently from unit test failures in your build automation.

  • @Matthew: I don't test the database server or the network stack, any more than I test the .NET BCL or CLR. Also, I've had no trouble at all localizing failures. Perhaps I should give a more-detailed example of the kind of test I mean. Commented Jun 6, 2011 at 12:36
  • @John When I said "test the database server and network stack," I mean that you are testing any code you are exercising. The .NET BCL/CLR must have a ticket into the unit test -- you can't write code without them! But, a test that talks to a database server is also exercising (i.e. testing) the server and network. I guess I should ask: How are you running your procedure tests? From an C# unit test method? A SQL script? How do you automate result reporting? Commented Jun 6, 2011 at 12:41
  • @Matthew: a normal NUnit test of a method in my class is exercising the .NET Framework, by your definition. Not by mine, as I am assuming that .NET works. Similarly, I am assuming that SQL Server works, and that the SqlCommand class works, and that the TCP transport works. I haven't been wrong about those yet. Commented Jun 6, 2011 at 12:47
  • 1
    @Matthew: if SQL Server and its built-in transports and features aren't working, then I've got larger problems than whether I'm unit testing or integration testing. I typically assume that the OS works as well, and that the speed of light is more or less constant within the frames my tests operate in. Commented Jun 6, 2011 at 13:04
  • 1
    @John I don't think you get my point, but oh well. Commented Jun 6, 2011 at 13:41

Is this integration testing or unit testing?

Where does the test run? On the database or via code-based test harness (JUnit)?

On the database then it is likely a unit test (as it is self contained), JUnit-like test harness then it connects to an external database and as such an integration test.

If this is "only" integration testing

Why the quotes? Integration tests are not some sort of beast you should never do.

does anyone have suggestions on how to do actual "unit tests" on these stored procedures?

I generally find the only reasonable unit for a stored procedure is the calling code¹ and the procedure. So I integration test the complete set in a set of tests which look just like unit tests.

A better question is why do you care [about the name].

As it allows you to split the tests out. Integration tests take longer to run, require additional set up and likely access to a resource shared by many tests (the database for example).

1) The calling code will have additional unit tests if required.

  • There is no additional setup. All of the setup is in the test itself. Also, these tests are pretty fast. The preparation in the test preamble has so far not needed to do more than delete 50 rows and then insert another dozen or so. Commented Jun 6, 2011 at 12:39
  • It starts a database if one is not running?
    – mlk
    Commented Jun 6, 2011 at 13:21
  • "Starts a database"? No, the tests use a configuration string stored in the configuration file for the tests, and one presumes that once the proper configuration string is determined, that it will continue to refer to a valid database within a valid database server. In an ideal world, these tests might run after a deployment (creation) of a new instance of the database, using the current DDL, also stored in source control. We're working towards that ideal. Commented Jun 6, 2011 at 13:28
  • It is a good ideal to get to. I have been working towards the same for some time. My point was that you do have start up tasks (starting a database). You can (and should) get your tests to not fail (Assert.Inconclusive in MSTest speak) if the tasks had not been performed. This area of testing a grey area of unit testing. Do write Unit Test-like tests, but accept they are integration tests.
    – mlk
    Commented Jun 6, 2011 at 15:04
  • 1
    I'm not sure what you mean by starting up a database. In my "ideal world", the deployment of a new, empty instance of the database would be part of the automated build, not part of the tests. Commented Jun 6, 2011 at 18:12

Microtest is a useful term that may help you sidestep the debate about terminology and whether your test is a unit-test or not.

A common definition of unit-test includes that the system under test is a small unit of functionality and you must be able to run all tests against it in memory, without help from file systems, networks, or database engines. If you need an actual database engine to run it, then it is not a unit test.

While this definition works very well for all application layers, it isn't very helpful to the bottomest database access layer. We have to recognize that it is special. If your tests test only small units of storage/retrieval/update/delete functionality, then your tests are clearly as valuable as "strict" unit tests written against the upper layers of your system.

  • This is where the controversy exists: I'm not testing the DAL: I'm testing the stored procedures and functions in the database itself. If the DAL is as simple as just setting up parameters and then calling the SP or function or SELECT, then I don't bother testing it. Commented Jun 6, 2011 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.