Testing Your Table Value Function
Yesterday we reviewed how we could write a test case for a Table Value Function without first writing the function. Today we are going to demonstrate a couple options for writing the function. You should be able to use the tests demonstrated in yesterdays editorial to test the resulting function.
In my databases I always include a table that contains a sequence of numbers from 0 to maybe 10,000. This table may be used to optimize many kinds of queries.
In my example for the table value function returning 12 date segments I utilize a sequence table to return those 12 segments. Here is the code I have written for the function TwelveMonthSegments_tfn. It assumes you pass into the function a datetime parameter of BasePeriod which is essentially the the start of the first time segment.
CREATE function TwelveMonthSegments_tfn(@BasePeriod DATETIME)
RETURNS Table
AS
RETURN
(
SELECT
Sequence.Number + 1 AS SegmentOrder
,DATEADD(mm, number, @BasePeriod) AS SegmentStart
,DATEADD(ms, -3, DATEADD(mm, number + 1, @BasePeriod))
AS SegmentEnd
FROM Sequence
WHERE Sequnce.Number BETWEEN 0 AND 11
)
If you don’t have a sequence table, you can use the recursive query recommended by Clifford to return the same 12 time segments.
CREATE function TwelveMonthSegments_tfn(@BasePeriod DATETIME)
RETURNS Table
AS
RETURN
(
WITH Segments(SegmentOrder, SegmentStart, SegmentEnd)
AS
(
SELECT
1 AS SegmentOrder
,@BasePeriod AS SegmentStart
,DATEADD(ms, -3, DATEADD(m, 1, @BasePeriod)) AS SegmentEnd
UNION ALL
SELECT
SegmentOrder + 1 AS SegmentOrder
,DATEADD(m, 1, SegmentStart) AS SegmentStart
,DATEADD(ms, -3, DATEADD(m, 2, SegmentStart)) AS SegmentEnd
FROM [Segments]
WHERE [SegmentOrder] < 12
)
SELECT SegmentOrder, SegmentStart, SegmentEnd
FROM Segments
)
At this point you need a function to compute the value for the variable @BasePeriod. The function needs to take a date, subtract 13 months, and set the value to the first day of that month at 00:00:00 hours. Using that value as the Base Period results in the beginning of 12 full months prior to the current date. That would make a nice little unit test also. With these two functions we can now begin to return the query for the desired report. Tomorrow we’ll dig into that.
To summarize the point of this series, in most cases there is no need to wait until code has been completed before you start writing your unit tests. With clarified requirements and a little communication you can begin writing tools to assert expected results before the first line of code is written implementing the requirements.
As always, please feel free to share your experiences in these discussions by writing to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)
Featured Script
Script SQL Database DDL
A tool which scripts out SQL Server 2000/2005 database objects to individual files in a manner which mimics Microsoft’s Visua… (read more)