How to test a function that selects from real tables

testingunit test

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.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [statistic].[daysByProject](
    @DateFrom DATE,
    @DateTo DATE,
)
returns
@ret table(ProjectID INT, IsFine TINYINT, Days smallint)
BEGIN

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

RETURN
END

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?

Best Answer

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.

Related Question