Occasionally, if you’re especially lucky, you’ll get into a situation where you have SQL procedures and functions that are so complicated that they require unit tests all of their own. They’ll have business logic embedded in them, and not testing them will leave a massive hole in your test coverage.
In this blog post I’m not going to describe how to do that - SSDT are quite well documented anyway. This is about how to deal with dates and times in SQL Server.
A new function
You’ll probably have a few places in your SQL script that call the following:
[sourcecode language=“SQL”] SELECT GETUTCDATE()
Or, you may even have the following:
[sourcecode language="SQL"]
SELECT GETDATE()
Which will presumably work well for what you want. Of course, the problem that you have here is, that for unit tests, this presents a variable factor in your test; that is, you’re not always testing the same procedure. Take the following segments of SQL for example:
[sourcecode language=“SQL”]
PROCEDURE MYPROC AS BEGIN DECLARE @today DATETIME DECLARE @hasEntriesAfterToday INT
SET @today = GETUTCDATE()
SELECT @hasEntriesAfterToday = COUNT(\*)
FROM dbo.MyTable t
WHERE t.Col1 > @today
IF (@hasEntriesAfterToday > 0)
select 'test'END GO
MyTable contains many entries after today, and my test checks that it returns 'test', so the test works, the code works and I'm going to bed.
**But what happens in a year's time?**
Let's say that the last entity in that table is 01/01/2015 (that way the post works in the US, too). As I write this, it is mid-way through June. So, I need to know what will happen on 2nd January 2015. If I do nothing then when it is 2nd January 2015 the test will start to fail, and I won't know why.
**Abstract the date**
When faced with this problem, my initial fix was as follows:
[sourcecode language="SQL"]
CREATE FUNCTION dbo.MyGetDate()
RETURNS DATETIME
AS
BEGIN
DECLARE @today DATETIME
SET @today = GETUTCDATE()
RETURN @today
END
And then simply change the above procedure to call this. That certainly works; however, as soon as you start to reference this function (for example, you set a default value for a date in a table), you’ll find that you’ll get stuck when you try to mock it out; consequently, you need a double layer:
[sourcecode language=“SQL”] CREATE FUNCTION dbo.MyGetDate2() RETURNS DATETIME AS BEGIN DECLARE @today DATETIME SET @today = GETUTCDATE()
RETURN @todayEND GO
CREATE FUNCTION dbo.MyGetDate() RETURNS DATETIME AS BEGIN DECLARE @today DATE SET @today = dbo.MyGetDate2()
RETURN @todayEND GO
What this then allows you to do is to replace the function of MyGetDate2 without affecting MyGetDate. This is a wrapper function to replace the DateTime:
``` csharp
internal static void OverrideDateTimeTest(SqlConnection cn, SqlTransaction tr, string newDateTime)
{
string sql =
"ALTER FUNCTION dbo.MyGetDate2( " +
") RETURNS datetime " +
"AS " +
"BEGIN " +
"DECLARE @value datetime " +
"SET @value = convert(datetime, '" + newDateTime + "') " +
"RETURN @value " +
"END";
using (SqlCommand cmd = new SqlCommand(sql, cn, tr))
{
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
And here’s the test:
[TestMethod]
public void MyTest()
{
DBWrapper.OverrideDateTimeTest(cn, tr, "2014-06-10 22:30:00.000");
Assert.AreEqual( …
The best part about this is that IN SQL SERVER DDL STATEMENTS CAN BE ROLLED BACK! Look at the following test:
[sourcecode language=“SQL”]
— 1 BEGIN TRAN GO
— 2 SELECT dbo.MyGetDate() GO
— 3 ALTER FUNCTION [dbo].MyGetDate2 RETURNS DATETIME AS BEGIN DECLARE @today DATETIME SET @today = GETUTCDATE()
RETURN @todayEND GO
— 4 SELECT dbo.MyGetDate() GO
— 5 ALTER FUNCTION [dbo].MyGetDate2 RETURNS DATETIME AS BEGIN DECLARE @today DATETIME SET @today = CONVERT(DATETIME, ‘2014-06-10 22:30:00.000’)
RETURN @todayEND GO
— 6 SELECT dbo.MyGetDate() GO
— 7 ROLLBACK TRAN GO
— 8 SELECT dbo.MyGetDate() GO
Okay - there's a fair amount of code, but the stages are as follows (numbered):
1. Start the transaction.
2. Show the existing implementation of MyGetDate2 (in case it's not what it should be).
3. Change MyGetDate2 to use GetUTCDate(), so it should be the same as before.
4. Check again - should still return the same as 2.
5. Change MgGetDate2 to return hard coded date.
6. Check that it now returns a hard coded date.
7. Rollback the transaction.
8. The transaction is rolled back, and so the function behaves as in 1.
**Conclusion**
So, we can include a date mock in our test and, should there be a problem, or when we're finished, it all gets rolled back. Just because I'm always cautious about such things, I've created a test that checks that the default implementation returns the current date, but you shouldn't need this.