Suppose you have 2 stored procedure, let's say a GetProducts and a GetProductDetails. Now, the GetProducts returns a list of products with a property that is "calculated" basing on additional calculation (the last time it was purchased, the number of resellers etc).

How can I be sure about the correctness of the data returned?

Do I have to call the GetProducts, then for each product call the GetProductDetails and finally check the correctness for each result? Or do I have to call the GetProductDetails only for a randomic product(s)?

  • Consider moving those stored procedures into your application or a data access layer. It's easier to test there, and easier to maintain there. Reserve stored procedures for things that need to run on the server, things like accounting reconciliations or statistical rollups. – Robert Harvey May 11 '19 at 17:20
  • As to your specific questions about GetProducts and GetProductDetails, I don't write unit tests for things like that (CRUD operations are unremarkable, from a programming perspective), but that's probably because I use an ORM to retrieve individual entities. – Robert Harvey May 11 '19 at 17:22
  • Right, but my whole application is based on stored procedures (trust me, there are reasons for not accessing directly the tables). – Bellons May 12 '19 at 19:55
  • You can use views to restrict access to tables. – Robert Harvey May 12 '19 at 19:56
  • 1
    It is basically as simple as the answer, but probably you don't know how to apply it to your case. So the actual question is not only about testing your procedures. The conventional approach is that the application is written and stored separately from the production environment, then it is built, tested, and all of that happens in isolation from the production instance and then it is deployed. I can imagine there may be questions how to do all of that for a database, but guessing them would not be useful. So maybe you refine your question and specify which step you have difficulties with. – max630 May 13 '19 at 3:55

You test the correctness (in a unit test, I suppose) the same way you would do it in any other layer: you provide some test data where you exactly know what the outcome of the calculation should be, then you write a unit test for GetProducts using this test data, and verify if the returned result matches the expected result.

There are two things which can make this harder in stored procedures than in other mainstream application programming languages:

  1. the (non-)availability of a convenient unit testing framework

  2. the (non-)availability of callback mechanisms which allow things like dependency injection

The first point can be mitigated by building your own unit testing infrastructure to the degree you need it, which is usually not too hard. For the second point, there are workarounds like this one.

  • "you provide some test data where you exactly know what the outcome of the calculation should be" is fine for "pure" functions, but for data that change over time is impossible. I mean, today the Product #1 may have 2 resellers, tomorrow 5. So, if I make the expected result "static", today the test may pass and tomorrow it may fail. – Bellons May 13 '19 at 7:06
  • 1
    @Bellons No, it won't. The resellers are part of the test data. – marstato May 13 '19 at 7:35
  • @Bellons: test data should be stored in test database, apart from production, with controlled changes to the data. And a well known principle for unit tests is to make sure each tests always works with a defined input. I thought this was so obvious that I did not see any necessity to mention it. – Doc Brown May 13 '19 at 13:57
  • "a well known principle for unit tests is to make sure each tests always works with a defined input" is exactly the part I'm referring. I've always thought that was referred to functions like find the sum of two integers or find the minimal path in a graph. But data on DB are meant to change over time. The purpose of unit testing is to test small pieces of code, isn't it? So you are testing functionalities, not data (even if in a test environment). – Bellons May 13 '19 at 14:27
  • A solution could be the usage of a throwaway db that creates data with expected properties in a "sandbox" environment. That way the stored could be tested with real input data and a real expected result, independent of the status of the whole system. – Bellons May 13 '19 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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