Sitemap
Jonathan Twite

Jonathan Twite

Astrophysicist turned full-stack .NET developer with interest in SQL tuning; web, desktop and mobile development; html, JavaScript, css and web accessibility

A simple SQL function tester

2 min readMay 5, 2025

Originally written 12 August 2021

Sometimes you need a quick method of testing the output of a function you have written, especially when you have fears about specific edge-cases that you want to check. For simple functions (the best type) this can be done in a simple one-line query utilising inline tables.

Get Jonathan Twite’s stories in your inbox

Join Medium for free to get updates from this writer.

Take for example, this function to determine whether a person is 18 or older on a specific date. The code has been lifted off of StackOverflow and so we should test some edge cases to ensure that it works.

CREATE FUNCTION dbo.fnIs18OrOver (@DateOfBirth DATE, @Date DATE)
RETURNS BIT
AS
BEGIN

IF((0 + FORMAT(@Date,'yyyyMMdd') - FORMAT(CAST(@DateOfBirth AS DATE),'yyyyMMdd') ) /10000) >= 18 RETURN 1;
RETURN 0;

END;
GO

It is possible to define a table inline within a query’s FROM statement using the VALUES keyword (MS docs):

SELECT *
FROM (VALUES ('Hi', 'Peter'), ('Hello', 'Paul')) AS tbl(Greeting, Name)
|Greeting|Name |
|--------|-----|
|Hi |Peter|
|Hello |Paul |

Using this, we can create a one-statement testing query. Add the WHERE clause back in to filter to only fails. It is possible to add another column with test description in case you start getting lost as to what the row is testing, but if you are getting that complex, you may want to explore the more complete SQL testing solutions available.

SELECT
tbl.TestDoB,
tbl.TestDate,
tbl.ExpectedIs18OrOver,
dbo.fnIs18OrOver(tbl.TestDoB, tbl.TestDate) AS ActualIs18OrOver
FROM
(VALUES
/* Test DoB on leap year */
('2000-02-29', '2018-02-27', 0),
('2000-02-29', '2018-02-28', 0),
('2000-02-29', '2018-03-01', 1),
('2000-02-29', '2018-03-02', 1),

/* Test target date on leap year */
('2002-02-27', '2020-02-29', 1),
('2002-02-28', '2020-02-29', 1),
('2002-03-01', '2020-02-29', 0),
('2002-03-02', '2020-02-29', 0)
) AS tbl(TestDoB, TestDate, ExpectedIs18OrOver);

/* Uncomment next line to only show failures */
/*WHERE tbl.ExpectedIs18OrOver <> dbo.fnIs18OrOver(tbl.TestDoB, tbl.TestDate);*/
|TestDoB   |TestDate  |ExpectedIs18OrOver|ActualIs18OrOver|
|----------|----------|------------------|----------------|
|2000–02–29|2018–02–27|0 |0 |
|2000–02–29|2018–02–28|0 |0 |
|2000–02–29|2018–03–01|1 |1 |
|2000–02–29|2018–03–02|1 |1 |
|2002–02–27|2020–02–29|1 |1 |
|2002–02–28|2020–02–29|1 |1 |
|2002–03–01|2020–02–29|0 |0 |
|2002–03–02|2020–02–29|0 |0 |

--

--

Jonathan Twite
Jonathan Twite

Written by Jonathan Twite

Astrophysicist turned full-stack .NET developer with interest in SQL tuning; web, desktop and mobile development; html, JavaScript, css and web accessibility