I've got a mega function that everybody is afraid to modify. So each fix adds a dozen of new IF lines. To me it feels that this function screams for proper testing.

Instead of the real function I'm including a really simplified example, because I believe that the crux of the challenge is that it's not only a function of its explicit parameters, but also of the state of some of the actual tables in the database.

ALTER FUNCTION [statistic].[daysByProject](
    @DateFrom DATE,
    @DateTo DATE,
@ret table(ProjectID INT, IsFine TINYINT, Days smallint)

insert into @ret(ProjectID, IsFine, Days) 
select ProjectID, IsFine, Days
from dbo.SomeRealTable


Is it possible to add tests to such a function? I've even heard of including tests in a transaction, apparently something akin to this

/* alter function */

  * Set up test case
  * Run
  * Raise error if test fails
  * Repeat with all test cases

/* Roll back on error */

Could something like that be possible so that (unless explicitly working around this) developers could only modify the function in a way that satisfies the requirements of test cases?

1 Answer 1


This is really difficult to answer. What works for others might not work for you and vice versa.

Personally i would set up a battery of repeatable test scripts. Start with a clean set of data, run a standard set of executions and then check the results are valid. Personally i would not put tests inside the code itself.

However, i would put validation checks inside your code so that it exits with sensible warnings, rather than corrupt data.

Im not a programmer, but i have learned that when a function or proc gets so complicated no-one understands it, it may be worth breaking it down into smaller more manageable functions.

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.