Unit Testing Databases with tSQLt Part 7 – testing date ranges in a WHERE clause

by Greg M Lucas on 13 January 2012

In Part 6, we looked at writing tests against a simple SELECT procedure checking that the correct columns were included and that results were returned in the correct order. In this post we will extend that SELECT procedure writing some tests for the WHERE clause, specifically dates and date ranges, and validating how default values are applied to some search predicates in the event of not being supplied by the caller.

Just to recap, this is what our Exception table now looks like and we have already written and tested a basic ExceptionReader procedure which returns a selection of these columns in the correct order.

CREATE TABLE [log4].[Exception]
(
  [ExceptionId]        int                  NOT NULL    IDENTITY( 1,1 )
, [UtcDate]            datetime             NOT NULL
, [SystemDate]         datetime             NOT NULL
-----------------------------------------------------------------------
, [ErrorContext]       nvarchar    ( 512 )  NOT NULL
, [ErrorNumber]        int                  NOT NULL
, [ErrorSeverity]      int                  NOT NULL
, [ErrorState]         int                  NOT NULL
, [ErrorProcedure]     nvarchar    ( 128 )  NOT NULL
, [ErrorLine]          int                  NOT NULL
, [ErrorMessage]       nvarchar    ( max )  NOT NULL
-----------------------------------------------------------------------
, [SessionId]          int                  NOT NULL
, [ServerName]         nvarchar    ( 128 )  NOT NULL
, [DatabaseName]       nvarchar    ( 128 )  NOT NULL
, [HostName]           nvarchar    ( 128 )  NOT NULL
, [ProgramName]        nvarchar    ( 128 )  NOT NULL
, [NTDomain]           nvarchar    ( 128 )  NOT NULL
, [NTUsername]         nvarchar    ( 128 )  NOT NULL
, [LoginName]          nvarchar    ( 128 )  NOT NULL
, [OriginalLoginName]  nvarchar    ( 128 )  NOT NULL
, [SessionLoginTime]   datetime             NULL
)

Having a stored procedure that just returns all exceptions since the beginning of time (assuming they’ve been retained) isn’t really that helpful. On a busy, poorly written, system, there may be hundreds of exceptions per hour being recorded. So our first user story for this article is “As a support analyst, I want to be able to narrow down the range of exceptions I can see to within a specific date/time range, possibly only a few seconds”. This immediately suggests that we need a couple of input parameters like this:

CREATE PROCEDURE [log4].[ExceptionReader]
(
  @StartDate  datetime
, @EndDate    datetime
)

This in turn implies that we’re going to end up with a where clause something like WHERE UtcDate BETWEEN @StartDate AND @EndDate so our next question is how do we go about writing tests for this. What is the minimum amount of test data that we can use to prove this piece of functionality? Dennis Lloyd Jr (blog | twitter) has written an interesting article on boundary test cases here which is part of his Test Case Heuristics series. Our search condition has a start point and an end point, this represents two boundaries meaning that the data in the [Exception] table can be split into three partitions, rows before the start date/time, rows after the end date/time and anything else in between. So to cover both boundaries I need four rows, with UtcDate values immediately before and after each boundary. This is what my first test looks like:

In this test, we define just the unique IDs and UTC dates for our four Exception rows. I’m keeping the dates fairly close to the boundaries as you may recall that the story specifically mentions a range of only a few seconds. You may notice that I’m using a couple of test helpers ([ExceptionBuilder] and [Make_ExceptionReader_ExceptionRowAndResultRow]) to reduce the amount of code I need to write. See a previous post here for how the Test Data Builder pattern can be used to write less code. Since I’m only interested in the unique IDs and UTC Dates in this test, that’s all the information I have to specify.

CREATE PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_filters_results_on_utc_date]
AS
BEGIN
    DECLARE @ExceptionId_A    int; SET @ExceptionId_A = 1
    DECLARE @ExceptionId_B    int; SET @ExceptionId_B = 2
    DECLARE @ExceptionId_C    int; SET @ExceptionId_C = 3
    DECLARE @ExceptionId_D    int; SET @ExceptionId_D = 4

    DECLARE @UtcDate_A datetime; SET @UtcDate_A = '20111121 10:00:01';
    DECLARE @UtcDate_B datetime; SET @UtcDate_B = '20111121 10:00:02';
    DECLARE @UtcDate_C datetime; SET @UtcDate_C = '20111121 10:00:03';
    DECLARE @UtcDate_D datetime; SET @UtcDate_D = '20111121 10:00:04';

    DECLARE @UtcDateStartRange datetime; SET @UtcDateStartRange = '20111121 10:00:02';
    DECLARE @UtcDateEndRange datetime; SET @UtcDateEndRange = '20111121 10:00:03';

    EXEC tSQLt.FakeTable 'log4', 'Exception';

--/////////////////////////////////////////////////////////////////////////////////////////////////
SetUp:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    --!
    --! Add the Exception rows and the rows we expect to see
    --! Call ExceptionBuilder to add a row that shouldn't be returned
    --! and Make_ExceptionReader_ExceptionRowAndResultRow for the rows we should see
    --!
    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ExceptionId  = @ExceptionId_A
            , @UtcDate      = @UtcDate_A

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ExceptionId  = @ExceptionId_B
            , @UtcDate      = @UtcDate_B

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ExceptionId  = @ExceptionId_C
            , @UtcDate      = @UtcDate_C

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ExceptionId  = @ExceptionId_D
            , @UtcDate      = @UtcDate_D

--/////////////////////////////////////////////////////////////////////////////////////////////////
Exercise:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    INSERT [ExceptionReaderTests].[actual]
    EXEC log4.ExceptionReader
          @StartDate = @UtcDateStartRange
        , @EndDate   = @UtcDateEndRange

--/////////////////////////////////////////////////////////////////////////////////////////////////
Assert:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    EXEC tSQLt.AssertEqualsTable '[ExceptionReaderTests].[expected]', '[ExceptionReaderTests].[actual]';
END

So after defining the dates I’m interested in, we add the four rows to the Exception table, two of which also get added to my [expected] results table (which is created automatically by tSQLt calling [ExceptionReaderTests].[SetUp] before each test – first introduced in Part 6). I then call log4.ExceptionReader to populate the [actual] results and finally call [AssertEqualsTable] to compare the actual results with the expected results.

Obviously, this first test will fail until we add the WHERE clause to the SELECT statement in the sproc (final complete version shown below).
So now that test passes but this change has broken the other two pre-existing tests for this procedure. This is one of the advantages offered by tSQLt in supporting the running of groups of tests and is also one of the underlying reasons for doing test-driven development in the first place. The sooner I know that a change in one area breaks something in another area, the easier it will be for me to find and fix the problem.

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                                                                |Result |
+--+----------------------------------------------------------------------------------------------+-------+
|1 |[ExceptionReaderTests].[Test_ExceptionReader_filters_results_on_utc_date]                     |Success|
|2 |[ExceptionReaderTests].[Test_ExceptionReader_returns_results_most_recent_record_at_top]       |Failure|
|3 |[ExceptionReaderTests].[Test_ExceptionReader_returns_single_row_result_set]                   |Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 3 test case(s) executed, 1 succeeded, 2 failed, 0 errored.
-----------------------------------------------------------------------------

If I look at those two failing tests (covered in Part 6) I can see that they don’t pass in a start or end search date/time which means that they won’t return the expected results. One of the other requirements for this procedure is to allow it to be used as simply as possible, i.e. EXEC log4.ExceptionReader;. This tells me that although adding these parameters has worked for the new test, it’s broken an earlier requirement to allow the procedure to be run without any inputs being specified. The solution is to define both parameters as optional (i.e. nullable) and then set them to some default value (last seven days in this case). So the start of [Exceptionreader] now looks like this:

CREATE PROCEDURE [log4].[ExceptionReader]
(
  @StartDate  datetime  = NULL
, @EndDate    datetime  = NULL
)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;

    --! Set defaults in absence of supplied values i.e. last seven days)
    IF @EndDate IS NULL SET @EndDate = GETUTCDATE();
    IF @StartDate IS NULL SET @StartDate = DATEADD(day, -7, @EndDate);
    ...
END

Of course, if I’m adding new code, I also need to write a new test to prove that if start band end dates are not specified, they default to the last seven days. The new test is here. The structure of this test is similar to [Test_ExceptionReader_filters_results_on_utc_date] above but instead of a date range a few seconds apart, we are testing boundary dates that are seven days apart.

CREATE PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_defaults_search_date_to_correct_range]
AS
BEGIN
    DECLARE @_ErrorNumber int; SET @_ErrorNumber = 0;
    DECLARE @_ErrorMessage nvarchar(2000);

    DECLARE @ExceptionId_A    int; SET @ExceptionId_A = 1
    DECLARE @ExceptionId_B    int; SET @ExceptionId_B = 2
    DECLARE @ExceptionId_C    int; SET @ExceptionId_C = 3
    DECLARE @ExceptionId_D    int; SET @ExceptionId_D = 4

    DECLARE @Now datetime; SET @Now = GETUTCDATE();

    DECLARE @UtcDate_A datetime; SET @UtcDate_A = DATEADD(day, -7, DATEADD(second, -1, @Now));
    DECLARE @UtcDate_B datetime; SET @UtcDate_B = DATEADD(day, -7, DATEADD(second, 1, @Now));
    DECLARE @UtcDate_C datetime; SET @UtcDate_C = DATEADD(second, -1, @Now);
    DECLARE @UtcDate_D datetime; SET @UtcDate_D = DATEADD(second, 5, @Now);

    EXEC tSQLt.FakeTable 'log4', 'Exception';

--/////////////////////////////////////////////////////////////////////////////////////////////////
SetUp:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ExceptionId  = @ExceptionId_A
            , @UtcDate      = @UtcDate_A

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ExceptionId  = @ExceptionId_B
            , @UtcDate      = @UtcDate_B

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ExceptionId  = @ExceptionId_C
            , @UtcDate      = @UtcDate_C

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ExceptionId  = @ExceptionId_D
            , @UtcDate      = @UtcDate_D
    ;

--/////////////////////////////////////////////////////////////////////////////////////////////////
Exercise:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    INSERT [ExceptionReaderTests].[actual]
    EXEC log4.ExceptionReader @StartDate = NULL, @EndDate = NULL;

--/////////////////////////////////////////////////////////////////////////////////////////////////
Assert:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    EXEC tSQLt.AssertEqualsTable '[ExceptionReaderTests].[expected]', '[ExceptionReaderTests].[actual]';
END

So now we’re back to four passing tests but we have another story to address “As a support analyst, I want to be able to specify a local (to me) start and end date/time and have the procedure adjust for this when searching against UTC date”. Given that Log4TSql has to be backwards compatible with SQL2005, one answer is to add another input parameter @TimeZoneOffset to allow the users to specify how many hows ahead of or behind UTC they are. Then when they supply start and end dates in local time, the [ExceptionReader] can use the supplied offset value to convert those times to UTC.

Among other tests that we might want to implement are:

The correct results are returned when the specified local time is ahead of UTC
The correct results are returned when the specified local time is behind of UTC
The correct results are returned when the specified local time is the same as UTC
What happens when a local time is specified without an offset

In this last scenario, I have elected to use the difference between system date and UTC date to identify the time zone offset.

So the first of these tests might look like this. Notice that we pinning the start and end time down to just one hour and we are getting as close as possible to our boundary conditions – 3 milliseconds being the smallest measurable unit for SQL Server datetime data type.

IF OBJECT_ID(N'[ExceptionReaderTests].[Test_ExceptionReader_date_filter_on_input_time_ahead_of_utc]', 'P') > 0
    DROP PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_date_filter_on_input_time_ahead_of_utc];
GO

CREATE PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_date_filter_on_input_time_ahead_of_utc]
AS
BEGIN
    DECLARE @ExceptionId_A    int; SET @ExceptionId_A = 1
    DECLARE @ExceptionId_B    int; SET @ExceptionId_B = 2
    DECLARE @ExceptionId_C    int; SET @ExceptionId_C = 3
    DECLARE @ExceptionId_D    int; SET @ExceptionId_D = 4

    DECLARE @TestOffset int; SET @TestOffset = 8;

    --! Define UTC start and end times
    DECLARE @UtcStartRange datetime; SET @UtcStartRange = '20101215 10:00:00.000';
    DECLARE @UtcEndRange datetime; SET @UtcEndRange = '20101215 11:00:00.000';

    DECLARE @UtcDate_A datetime; SET @UtcDate_A = DATEADD(millisecond, -3, @UtcStartRange);
    DECLARE @UtcDate_B datetime; SET @UtcDate_B = @UtcStartRange;
    DECLARE @UtcDate_C datetime; SET @UtcDate_C = @UtcEndRange;
    DECLARE @UtcDate_D datetime; SET @UtcDate_D = DATEADD(millisecond, 3, @UtcEndRange);

    --!
    --! So if a user is 8 hours ahead of UTC, they're going to supply a local date time
    --! and +8 as their offset.  ExpectionReader needs to convert the supplied local
    --! times back to UTC using the supplied offset (actually a reverse calc)
    --!
    DECLARE @InputStartRange datetime; SET @InputStartRange = DATEADD(hour, @TestOffset, @UtcStartRange);
    DECLARE @InputEndRange datetime; SET @InputEndRange = DATEADD(hour, @TestOffset, @UtcEndRange);

    EXEC tSQLt.FakeTable 'log4', 'Exception';

--/////////////////////////////////////////////////////////////////////////////////////////////////
SetUp:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    --!
    --! Add the all the source rows to Exception
    --! We call Make_ExceptionReader_ExceptionRowAndResultRow for rows we are expecting
    --! to actually see and just call ExceptionBuilder for rows we expect to be exluded
    --! from the result set.
    --!
    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ExceptionId  = @ExceptionId_A
            , @UtcDate      = @UtcDate_A

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ExceptionId  = @ExceptionId_B
            , @UtcDate      = @UtcDate_B

    EXEC [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ExceptionId  = @ExceptionId_C
            , @UtcDate      = @UtcDate_C

    EXEC [TestDataBuilder].[ExceptionBuilder]
              @ExceptionId  = @ExceptionId_D
            , @UtcDate      = @UtcDate_D
    ;

--/////////////////////////////////////////////////////////////////////////////////////////////////
Exercise:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    INSERT [ExceptionReaderTests].[actual]
    EXEC log4.ExceptionReader
          @StartDate      = @InputStartRange
        , @EndDate        = @InputEndRange
        , @TimeZoneOffset = @TestOffset

--/////////////////////////////////////////////////////////////////////////////////////////////////
Assert:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    EXEC tSQLt.AssertEqualsTable '[ExceptionReaderTests].[expected]', '[ExceptionReaderTests].[actual]';
END

Something to note about this test for local time being ahead of UTC is that the only thing that would need to change in order to validate the local time being the same as or less than UTC would be the value of @TestOffset. This is a good example of when to refactor your test code to avoid having multiple, near-identical test procedures. So I can move all the setup logic out to the following procedure like this

CREATE PROCEDURE [ExceptionReaderTests].[Setup_ExceptionReader_date_filter_test_on_timezone_offset]
(
  @TestOffset      int                = 0
, @StartRange      datetime           = NULL OUT
, @EndRange        datetime           = NULL OUT
, @ErrorMessageOut nvarchar    ( 2000 )  = ''   OUT
)
AS
BEGIN
    DECLARE @ReturnValue int; SET @ReturnValue = 0;

    BEGIN TRY
        DECLARE @ExceptionId_A    int; SET @ExceptionId_A = 1
        DECLARE @ExceptionId_B    int; SET @ExceptionId_B = 2
        DECLARE @ExceptionId_C    int; SET @ExceptionId_C = 3
        DECLARE @ExceptionId_D    int; SET @ExceptionId_D = 4

        --! Define the UTC start and end times
        DECLARE @UtcStartRange datetime; SET @UtcStartRange = '20101215 10:00:00.000';
        DECLARE @UtcEndRange datetime; SET @UtcEndRange = '20101215 11:00:00.000';

        DECLARE @UtcDate_A datetime; SET @UtcDate_A = DATEADD(millisecond, -3, @UtcStartRange);
        DECLARE @UtcDate_B datetime; SET @UtcDate_B = @UtcStartRange;
        DECLARE @UtcDate_C datetime; SET @UtcDate_C = @UtcEndRange;
        DECLARE @UtcDate_D datetime; SET @UtcDate_D = DATEADD(millisecond, 3, @UtcEndRange);

        SET @StartRange = DATEADD(hour, @TestOffset, @UtcStartRange);
        SET @EndRange = DATEADD(hour, @TestOffset, @UtcEndRange);

        EXEC tSQLt.FakeTable 'log4', 'Exception';

        EXEC @ReturnValue = [TestDataBuilder].[ExceptionBuilder]
                  @ExceptionId  = @ExceptionId_A
                , @UtcDate      = @UtcDate_A

        IF @ReturnValue = 0
            EXEC @ReturnValue = [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
                      @ExceptionId  = @ExceptionId_B
                    , @UtcDate      = @UtcDate_B

        IF @ReturnValue = 0
            EXEC @ReturnValue = [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
                      @ExceptionId  = @ExceptionId_C
                    , @UtcDate      = @UtcDate_C

        IF @ReturnValue = 0
            EXEC @ReturnValue = [TestDataBuilder].[ExceptionBuilder]
                      @ExceptionId  = @ExceptionId_D
                    , @UtcDate      = @UtcDate_D
    END TRY
    BEGIN CATCH
        SET @ReturnValue = ERROR_NUMBER();
        SET @ErrorMessageOut = '[ExceptionReaderTests].[Setup_ExceptionReader_date_filter_test_on_timezone_offset] ERROR: ' + COALESCE(ERROR_MESSAGE(), 'No Error Message');

        EXEC tSQLt.Fail @ErrorMessageOut;
    END CATCH

    --! Make sure all steps completed successfully
    IF @ReturnValue > 0
        EXEC tSQLt.Fail @ErrorMessageOut;

    RETURN (@ReturnValue);
END

This means that the test for local time being ahead of UTC now looks like this:
(and the tests for less than or the same as UTC will still be near-dientical but much simpler)

CREATE PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_date_filter_on_input_time_ahead_of_utc]
AS
BEGIN
    DECLARE @TestOffset int; SET @TestOffset = 8;
    DECLARE @InputStartRange datetime, @InputEndRange datetime;

    EXEC [ExceptionReaderTests].[Setup_ExceptionReader_date_filter_test_on_timezone_offset]
          @TestOffset  = @TestOffset
        , @StartRange  = @InputStartRange OUT
        , @EndRange    = @InputEndRange OUT

--/////////////////////////////////////////////////////////////////////////////////////////////////
Exercise:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    INSERT [ExceptionReaderTests].[actual]
    EXEC log4.ExceptionReader
          @StartDate      = @InputStartRange
        , @EndDate        = @InputEndRange
        , @TimeZoneOffset = @TestOffset

--/////////////////////////////////////////////////////////////////////////////////////////////////
Assert:
--/////////////////////////////////////////////////////////////////////////////////////////////////

    EXEC tSQLt.AssertEqualsTable '[ExceptionReaderTests].[expected]', '[ExceptionReaderTests].[actual]';
END

The logic to pass these additional tests looks like this:

So if a user is 8 hours behind UTC, they’re going to supply a local date time and -8 as their offset. Similarly, if the user is 4 hours ahead of UTC, they will specify an offset value of +4. [ExpectionReader] will convert the supplied local times back to UTC using the supplied offset (actually a reverse calc)

CREATE PROCEDURE [log4].[ExceptionReader]
(
  @StartDate            datetime           = NULL
, @EndDate              datetime           = NULL
, @TimeZoneOffset       smallint           = NULL
)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;

    --! Set defaults in absence of supplied values i.e. last seven days)
    IF @EndDate IS NULL SET @EndDate = GETUTCDATE();
    IF @StartDate IS NULL SET @StartDate = DATEADD(day, -7, @EndDate);

    --! NEW_CODE_STARTS_HERE

    --! If no time zone offset is supplied, use the difference between system time and UTC
    SET @TimeZoneOffset    = COALESCE(@TimeZoneOffset,  DATEDIFF(hour, GETDATE(), GETUTCDATE()))
    SET @StartDate    = DATEADD(hour, @TimeZoneOffset * -1, @StartDate)
    SET @EndDate    = DATEADD(hour, @TimeZoneOffset * -1, @EndDate)

    --! NEW_CODE_ENDS_HERE

So now our test results look like this:

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                                                                |Result |
+--+----------------------------------------------------------------------------------------------+-------+
|1 |[ExceptionReaderTests].[Test_ExceptionReader_date_filter_on_input_time_ahead_of_utc]          |Success|
|2 |[ExceptionReaderTests].[Test_ExceptionReader_date_filter_on_input_time_behind_of_utc]         |Success|
|3 |[ExceptionReaderTests].[Test_ExceptionReader_date_filter_on_input_time_same_as_utc]           |Success|
|4 |[ExceptionReaderTests].[Test_ExceptionReader_filters_on_local_offset_when_offset_not_supplied]|Success|
|5 |[ExceptionReaderTests].[Test_ExceptionReader_filters_results_on_utc_date]                     |Success|
|6 |[ExceptionReaderTests].[Test_ExceptionReader_returns_results_most_recent_record_at_top]       |Success|
|7 |[ExceptionReaderTests].[Test_ExceptionReader_returns_single_row_result_set]                   |Success|
|8 |[ExceptionReaderTests].[Test_ExceptionReader_defaults_search_date_to_correct_range]           |Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 8 test case(s) executed, 7 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

So our most recent changes have broken another earlier test. [Test_ExceptionReader_defaults_search_date_to_correct_range] is designed to prove that when the start and end date are supplied as NULL, the search period will cover the last seven days starting right now. However, the logic we added to set the value of @TimeZoneOffset to the difference between system date and UTC in the absence of a supplied value means that there is a calculation going on that I hadn’t accounted for. Admittedly this has only been hilighted because I am currently working in Singapore which is GMT +8. If I was working in the UK right now (British winter time), this test wouldn’t fail.

One way to address this is if no offset is supplied, to set it to zero but only if either the start or end date is also NULL, like this:

IF @EndDate IS NULL OR @StartDate IS NULL SET @TimeZoneOffset = COALESCE(@TimeZoneOffset, 0);

This change now results in all tests passing. For reference, the completed [ExceptionReader] now looks like this:

CREATE PROCEDURE [log4].[ExceptionReader]
(
  @StartDate       datetime  = NULL
, @EndDate         datetime  = NULL
, @TimeZoneOffset  smallint  = NULL
)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;

    --! Working variables
    DECLARE @Error int; SET @Error = 0;

    --! Set defaults in absence of supplied values i.e. last seven days)
    --! If either date is NULL, we're going to work off UTC so set the
    --! timezone offset to zero if it hasn't been specified
    IF @EndDate IS NULL OR @StartDate IS NULL SET @TimeZoneOffset = COALESCE(@TimeZoneOffset, 0);
    IF @EndDate IS NULL SET @EndDate = GETUTCDATE();
    IF @StartDate IS NULL SET @StartDate = DATEADD(day, -7, @EndDate);

    --! If no time zone offset is supplied, use the difference between system time and UTC
    SET @TimeZoneOffset = COALESCE(@TimeZoneOffset,  DATEDIFF(hour, GETDATE(), GETUTCDATE()))
    SET @StartDate      = DATEADD(hour, @TimeZoneOffset * -1, @StartDate)
    SET @EndDate        = DATEADD(hour, @TimeZoneOffset * -1, @EndDate)

    SELECT
          ExceptionId
        , UtcDate
        , SystemDate
        , ErrorNumber
        , ErrorContext
        , ErrorMessage
        , ErrorSeverity
        , ErrorState
        , ErrorProcedure
        , ErrorLine
        , SessionId
        , ProgramName
        , NTDomain
        , NTUsername
        , LoginName
    FROM
        [log4].[Exception]
    WHERE
        UtcDate BETWEEN @StartDate AND @EndDate
    ORDER BY
        ExceptionId DESC

    SELECT @Error = @@ERROR;

    SET NOCOUNT OFF;

    RETURN(@Error);
END

In this post we looked at how to write tests to confirm that parameterised start and end dates were correctly applied in a WHERE clause and also testing the effects of internal procedure logic when different default values are applied. Finally, we learnt how followng test driven development can help hilight code changes for new requirements that break existing functionality, allowing fixes to be implemented whilst the breaking change is still fresh in our minds. In Part 8, we will look at testing string searches as part of the same [ExceptionReader] procedure.

Log4TSql is an open source logging framework for SQL Server 2005+ written by Greg M Lucas on behalf of data-centric solutions ltd and licensed for public use under the GNU Lesser General Public License. You can download the latest version of this library along with all the source code and unit tests from sourceforge. Please feel free to use and modify the library as you wish under the terms of the license. I am always interested in hearing how other people or organisations have put this framework to use.

{ 2 comments }

Patrick February 4, 2012 at 21:45

Hi Greg

Thanks for the write up.
A couple of things to consider:
1 – “Exception” is a future reserved keyword word in SQL Server – you may want to rename your table see
http://msdn.microsoft.com/en-us/library/ms189822.aspx
2- You can also write ErrorMessageOut as
SET @ErrorMessageOut = OBJECT_NAME(@@PROCID) + ‘ ERROR: ‘ + COALESCE(ERROR_MESSAGE(), ‘No Error Message’);

Cheers

Greg M Lucas February 6, 2012 at 12:29

Patrick,

Thanks for the heads-up re “Exception” – I will have look at changing that in some future release of Log4TSql. Thanks for supplying the link.

WRT your alternative method for populating @ErrorMessageOut, I admit I was being a little lazy. Although my long-hand version includes the schema name to make identifying the failing test easy which OBJECT_NAME() doesn’t provide. To be correct I should probably have declared a variable at the top of the sproc like this:

DECLARE @FunctionName nvarchar(255) = QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + ‘.’ + QUOTENAME(OBJECT_NAME(@@PROCID));

Then set the output message like this…

SET @ErrorMessageOut = @FunctionName + ‘ ERROR: ‘ + COALESCE(ERROR_MESSAGE(), ‘No Error Message’);

Thank you again for your input.

Comments on this entry are closed.

Previous post:

Next post: