Unit Testing Databases with tSQLt Part 6 – testing a SELECT stored procedure

by Greg M Lucas on 7 December 2011

In Part 5, we looked at writing tests to prove that one stored procedure calls another and how test-driven development can help us refactor code. In Part 6 we are going to write some initial tests for a stored procedure that returns data as part of the Log4TSql open source logging library for SQL Server 2005+ . This includes validating column order and also the order in which the rows are returned. We will also look at other ways of refactoring our tests to reduce the amount of test code we need to write and so increase the ROI from following test-driven database development.

Just to recap, this is what our Exception table now looks like and we have already written and tested the ExceptionHandler sproc to populate this table with details of any SQL Server errors caught within a TRY… CATCH… block.

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 )  NULL
, [ProgramName]        nvarchar    ( 128 )  NULL
, [NTDomain]           nvarchar    ( 128 )  NULL
, [NTUsername]         nvarchar    ( 128 )  NULL
, [LoginName]          nvarchar    ( 128 )  NULL
, [OriginalLoginName]  nvarchar    ( 128 )  NULL
, [SessionLoginTime]   datetime             NULL
)

Testing a simple SELECT statement

At it’s simplest, we just need a stored procedure that returns a selection of the above columns; for such a simple test, we don’t care about filters or getting multiple rows. We just want to ensure that we get a result set back and that it contains the correct columns in the right order.  Although column order shouldn’t matter to whatever process calls this stored procedure, we can’t know it for certain so we do need to test that.  Also, if now, or at some point in the future any columns are aliased and the columns get mixed up we want this test to fail. Lets start by creating a new class/schema to hold these tests.

EXEC tSQLt.NewTestClass 'TestExceptionReader';

Our first test will look something like this, we need an [expected] table to record the results we are expecting to see, and an [actual] table to record the results we actually get. In this scenario, I prefer to define all the inputs as variables – even though we’re only using them twice, it still avoids the possibilty of typos or copy & paste errors and, if we keep the variable names the same as the column names, it’s relatively easy to ensure the expected values start off in the right expected columns.  We then need to add a row to the the [Exception] table that we can retrieve then add a row to the [expected] table to describe what we expect the result set to look like.

CREATE PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_returns_single_row_result_set]
AS
BEGIN
    --! Create tables to hold our expected and actual result sets
    IF OBJECT_ID('[ExceptionReaderTests].[expected]') IS NOT NULL DROP TABLE [ExceptionReaderTests].[expected];
    IF OBJECT_ID('[ExceptionReaderTests].[actual]') IS NOT NULL DROP TABLE [ExceptionReaderTests].[actual];

    CREATE TABLE [ExceptionReaderTests].[expected]
    (
      [ExceptionId]     int                NULL
    , [UtcDate]         datetime           NULL
    , [SystemDate]      datetime           NULL
    , [ErrorNumber]     int                NULL
    , [ErrorContext]    nvarchar  ( 512 )  NULL
    , [ErrorMessage]    nvarchar  ( max )  NULL
    , [ErrorSeverity]   int                NULL
    , [ErrorState]      int                NULL
    , [ErrorProcedure]  nvarchar  ( 128 )  NULL
    , [ErrorLine]       int                NULL
    , [SessionId]       int                NULL
    , [ProgramName]     nvarchar  ( 128 )  NULL
    , [NTDomain]        nvarchar  ( 128 )  NULL
    , [NTUsername]      nvarchar  ( 128 )  NULL
    , [LoginName]       nvarchar  ( 128 )  NULL
    );

    CREATE TABLE [ExceptionReaderTests].[actual]
    (
      [ExceptionId]     int                NULL
    , [UtcDate]         datetime           NULL
    , [SystemDate]      datetime           NULL
    , [ErrorNumber]     int                NULL
    , [ErrorContext]    nvarchar  ( 512 )  NULL
    , [ErrorMessage]    nvarchar  ( max )  NULL
    , [ErrorSeverity]   int                NULL
    , [ErrorState]      int                NULL
    , [ErrorProcedure]  nvarchar  ( 128 )  NULL
    , [ErrorLine]       int                NULL
    , [SessionId]       int                NULL
    , [ProgramName]     nvarchar  ( 128 )  NULL
    , [NTDomain]        nvarchar  ( 128 )  NULL
    , [NTUsername]      nvarchar  ( 128 )  NULL
    , [LoginName]       nvarchar  ( 128 )  NULL
    );

    --! Define a different value for each column so we can validate column order
    DECLARE @ExceptionId int; SET @ExceptionId = 99;
    DECLARE @UtcDate datetime; SET @UtcDate = GETUTCDATE();
    DECLARE @SystemDate datetime; SET @SystemDate = GETDATE();
    DECLARE @ErrorContext nvarchar(512); SET @ErrorContext = 'Oops!';
    DECLARE @ErrorNumber int; SET @ErrorNumber = 60000;
    DECLARE @ErrorSeverity int; SET @ErrorSeverity = 16;
    DECLARE @ErrorState int; SET @ErrorState = 10;
    DECLARE @ErrorProcedure nvarchar(128); SET @ErrorProcedure = 'SomeProceOrOther';
    DECLARE @ErrorLine int; SET @ErrorLine = 2;
    DECLARE @ErrorMessage nvarchar(max); SET @ErrorMessage = 'Who knows?';
    DECLARE @SessionId int; SET @SessionId = 26;
    DECLARE @ServerName nvarchar(128); SET @ServerName = 'Tom';
    DECLARE @DatabaseName nvarchar(128); SET @DatabaseName = 'Dick';
    DECLARE @HostName nvarchar(128); SET @HostName = 'Harry';
    DECLARE @ProgramName nvarchar(128); SET @ProgramName = 'Matthew';
    DECLARE @NTDomain nvarchar(128); SET @NTDomain = 'Mark';
    DECLARE @NTUsername nvarchar(128); SET @NTUsername = NULL;
    DECLARE @LoginName nvarchar(128); SET @LoginName = 'Luke';
    DECLARE @OriginalLoginName nvarchar(128); SET @OriginalLoginName = 'John';
    DECLARE @SessionLoginTime datetime; SET @SessionLoginTime = DATEADD(minute, -1, GETDATE());

    --! Isolate this test from the real data
    EXEC tSQLt.FakeTable 'log4.Exception'

    --! Add a row to the faked Exception table that we can retrieve
    INSERT [log4].[Exception]
    (
      [ExceptionId]
    , [UtcDate]
    , [SystemDate]
    , [ErrorContext]
    , [ErrorNumber]
    , [ErrorSeverity]
    , [ErrorState]
    , [ErrorProcedure]
    , [ErrorLine]

    , [ErrorMessage]
    , [SessionId]
    , [ServerName]
    , [DatabaseName]
    , [HostName]
    , [ProgramName]
    , [NTDomain]
    , [NTUsername]
    , [LoginName]
    , [OriginalLoginName]
    , [SessionLoginTime]
    )
    VALUES
    (
      @ExceptionId
    , @UtcDate
    , @SystemDate
    , @ErrorContext
    , @ErrorNumber
    , @ErrorSeverity
    , @ErrorState
    , @ErrorProcedure
    , @ErrorLine
    , @ErrorMessage
    , @SessionId
    , @ServerName
    , @DatabaseName
    , @HostName
    , @ProgramName
    , @NTDomain
    , @NTUsername
    , @LoginName
    , @OriginalLoginName
    , @SessionLoginTime
    )

    --! Record what our results should look like
    INSERT [ExceptionReaderTests].[expected]
    (
      [ExceptionId]
    , [UtcDate]
    , [SystemDate]
    , [ErrorNumber]
    , [ErrorContext]
    , [ErrorMessage]
    , [ErrorSeverity]
    , [ErrorState]
    , [ErrorProcedure]
    , [ErrorLine]
    , [SessionId]
    , [ProgramName]
    , [NTDomain]
    , [NTUsername]
    , [LoginName]
    )
    VALUES
    (
      @ExceptionId
    , @UtcDate
    , @SystemDate
    , @ErrorNumber
    , @ErrorContext
    , @ErrorMessage
    , @ErrorSeverity
    , @ErrorState
    , @ErrorProcedure
    , @ErrorLine
    , @SessionId
    , @ProgramName
    , @NTDomain
    , @NTUsername
    , @LoginName
    );

    --! Exercise (and record what the results actually look like)
    INSERT [ExceptionReaderTests].[actual]
    (
      [ExceptionId]
    , [UtcDate]
    , [SystemDate]
    , [ErrorNumber]
    , [ErrorContext]
    , [ErrorMessage]
    , [ErrorSeverity]
    , [ErrorState]
    , [ErrorProcedure]
    , [ErrorLine]
    , [SessionId]
    , [ProgramName]
    , [NTDomain]
    , [NTUsername]
    , [LoginName]
    )
    EXEC log4.ExceptionReader

    DECLARE @expectedRowCount int; SET @expectedRowCount = 1;
    DECLARE @actualRowCount int; SET @actualRowCount = (SELECT COUNT(*) FROM [ExceptionReaderTests].[actual])

    --! Assert
    EXEC tSQLt.AssertEquals @expectedRowCount, @actualRowCount, 'The number of rows returned is incorrect';

    EXEC tSQLt.AssertEqualsTable '[ExceptionReaderTests].[expected]', '[ExceptionReaderTests].[actual]', 'The column order in the result set is incorrect';
END

We end this test with two assertions, specifying a reason for failure on each so we can easily differentiate between them if this test fails.  We start by evaluating that the number of rows returned is correct, then move on to validating the column order/content.  Having both assertions in such a simple test is probably overkill but it serves to demonstrate how, when dealing with larger, more complicated result sets you can carry out a faster, simpler assertion before a more complex one.

This test also introduces a tSQLt feature not yet covered in this series: tSQLt.FakeTable. This is one more great reason why I like using tSQLt for test-driven development as by “faking” or mocking the table I can test it in complete isolation.

Pain Relief for Database Unit Testing

One of the things that makes writing tests for a fully fledged relational database more challenging is the presence of foreign keys (and often other constraints), for example an OrderDetail table has references to the Product and OrderHeader table, the Product table references ProductType, OrderHeader references Employee, Customer, Dispatcher etc. etc. By the time you’ve added all the required data for one simple test, it’s time to go home and when you get in the next morning you decide it’s not worth the effort and go back to “debug later” instead of “test first” development. The call to EXEC tSQLt.FakeTable '[log4].[Exception]'; temporarily renames the original Exception table and creates a copy with the same structure called Exception but without any of the constraints. So views, functions and most procedures that reference this table continue to work as they should and we can write isolated tests against data that we have absolute control over without having to populate loads of other unrelated tables or supply extra data just to comply with some constraint.  tSQLt also allows specific constraints such as foreign keys to be applied to the fake table so you can test specific database rules in the same level of isolation but that is the topic for a future post.

So once we have our fake Exception table we can add a row to it, then call [ExceptionReader] to get that row back. We then compare the number of rows returned with the number of rows we expected followed by the call to [tSQLt].[AssertEqualsTable] to validate column order.

The minimum code required to pass this test is as follows:

CREATE PROCEDURE [log4].[ExceptionReader]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
          ExceptionId
        , UtcDate
        , SystemDate
        , ErrorNumber
        , ErrorContext
        , ErrorMessage
        , ErrorSeverity
        , ErrorState
        , ErrorProcedure
        , ErrorLine
        , SessionId
        , ProgramName
        , NTDomain
        , NTUsername
        , LoginName
    FROM
        [log4].[Exception];

    SET NOCOUNT OFF;

    RETURN;
END

As stored procedures go this is pretty limited so we’re going to need to add some more functionality to make it at all useful; and each new feature we add will need to be supported by at least one test. First of all, if there are lots of exceptions being recorded, we really want to see the latest ones at the top of the result set, i.e. in reverse date or identity order.

Red Green Refactor – Don’t Repeat Yourself (DRY)

But before we start writing more tests, let’s just take another look at [ExceptionReaderTests].[Test_ExceptionReader_returns_single_row_result_set]. You may be thinking that’s an awful lot of code to write for just one test – and asking yourself how much more code will you need to write for all the tests we might eventually need to cover [ExceptionReader] properly?  Don’t worry, I agree entirely.  Having to reproduce all this code multiple times would make following TDD for this, or indeed any project completely impractical so it’s time for a bit of refactoring.  An awful lot of that set up code in the first test could be easily refactored out into separate procedures following DRY principles.

Although you wouldn’t ordinarily start refactoring test code for just one unit test, we know we’re going to be writing several more tests that will be similar in terms of setup to the first test. To achieve this refactoring we’re going to leverage another tSQLt feature that we haven’t talked about yet in this series and also borrow a pattern from the object oriented world called Test Data Builder (TDB) which I have blogged about previously.

Automatic Setup

Object oriented unit testing tools such as NUnit support the concept of a SetUp method that will be automatically run before each test in a class. Fortunately, tSQLt offers the same functionality, whenever you run tests in any class (a.k.a. schema), tSQLt will look for a procedure called “SetUp” in that schema and run it before each test in that class.

In the case of our [ExceptionReaderTests] class, the one common thing we are likely to need to do before every test is to create the [expected] and [actual] results tables with which we compare result sets. So the first thing we can do to make our lives easier is to move the responsibilty for recreating these tables to a stored procedure called [ExceptionReaderTests].[SetUp].

I haven’t reproduced the code within this post but if you’re interested you can view it here. One thing to note if you do look at this procedure is how I use an auto-incrementing column with a clustered index to preserve the order in which rows are added to the table.  The tSQLt framework will take care of the set up details for us, running this procedure before, and within the same transaction as each individual test within the [ExceptionReaderTests] class.

NUnit also supports a TearDown method which is run after every test but as tSQLt runs each individual test in it’s own transaction which is rolled back on completion of the test, tSQLt doesn’t need a TearDown equivalent.

Preparing Sample Data Easily

The other thing we’re going to use is the Test Data Builder (TDB) pattern which, in a nutshell, will allow us to create as many rows of data as we need, specifying only the column values we need for a specific test.

For example, if we were testing a filter on the [Exception].[ErrorProcedure] column, that (along with ExceptionId to identify correct matching rows) is the only column that actually needs to be populated. TDB allows us to do exactly that ignoring the remaining columns.  So, we’ll need a stored procedure called [TestDataBuilder].[ExceptionBuilder].

Basically the idea is that [ExceptionBuilder] has a parameter for every column on the [Exception] table.  All of these parameters have default values, typically “” for string parameters or zero for numeric inputs.  But, as [Exception] has an IDENTITY column and a couple of columns with DEFAULTS defined, these are defined as OUTPUT parameters.  [ExceptionBuilder] contains everything necessary to add a valid row to the [Exception] table with the absolute minimum (and so predictable) values, creating missing values such as IDENTITY as necessary. This allows us to add a valid row to the [Exception] with as little code as this:

EXEC [TestDataBuilder].[ExceptionBuilder] @ExceptionId = @NewId OUT

As part of this refactoring, we’re also going to need a couple of test helpers, detailed below.

[ExceptionReaderTests].[Make_ExceptionReader_ResultRow] – this sproc takes the specified inputs (or defaults if not provided) and adds a row to the [expected] table created by [ExceptionReaderTests].[SetUp].

[ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow] – first calls [ExceptionBuilder] to add a row to the Exception table, collecting any relevant outputs (such as the auto-increment ID) then calls [Make_ExceptionReader_ResultRow] to put the relevant values into our [expected] results table.

Refactored Test

If you are only going to write one or two tests it’s probably not going to be worth creating the TDB procedure or test helpers but if you’re writing a lot of tests requiring the same or similar setup, a little bit of work up front will save you loads of time later.  After creating my TDB ExceptionBuilder and the other helpers, I can refactor our first test: [Test_ExceptionReader_returns_single_row_result_set], then re-run the test to ensure that the refactoring hasn’t broken anything. Our refactored test now looks like this:

CREATE PROCEDURE [ExceptionReaderTests].[Test_ExceptionReader_returns_single_row_result_set]
AS
BEGIN
    DECLARE @ReturnValue int, @FailMessage nvarchar(2000);

    --! Isolate this test from the real data
    EXEC tSQLt.FakeTable 'log4.Exception'

    --! Create a row that we can retrieve and store the expected result row in a single step.
    --! Still need to populate all columns with different values so we can test result set
    --! column order
    EXEC @ReturnValue = [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ErrorContext      = 'Oops!'
            , @ErrorNumber       = 60000
            , @ErrorSeverity     = 16
            , @ErrorState        = 10
            , @ErrorProcedure    = 'SomeProceOrOther'
            , @ErrorLine         = 2
            , @ErrorMessage      = 'Who knows?'
            , @SessionId         = 26
            , @ServerName        = 'Tom'
            , @DatabaseName      = 'Dick'
            , @HostName          = 'Harry'
            , @ProgramName       = 'Matthew'
            , @NTDomain          = 'Mark'
            , @NTUsername        = 'Luke'
            , @LoginName         = NULL
            , @OriginalLoginName = 'John'
            , @ErrorMessageOut   = @FailMessage    OUT

    --! Exercise (and record what the results actually look like)
    --! Take advantage of the fact that SQL Server is bright enough to
    --! skip the IDENTITY column on [actual] so we don't need a column list
    INSERT [ExceptionReaderTests].[actual]
    EXEC log4.ExceptionReader

    DECLARE @expectedRowCount int; SET @expectedRowCount = 1;
    DECLARE @actualRowCount int; SET @actualRowCount = (SELECT COUNT(*) FROM [ExceptionReaderTests].[actual])

    --! Assert
    EXEC tSQLt.AssertEquals @expectedRowCount, @actualRowCount, 'The number of rows returned is incorrect';

    EXEC tSQLt.AssertEqualsTable '[ExceptionReaderTests].[expected]', '[ExceptionReaderTests].[actual]', 'The column order in the result set is incorrect';
END

We have reduced the code in this test from nearly 300 lines to less than 50.  Hopefully you will agree that the new code is easier to understand and certainly a lot quicker to write.

Testing the order in which results are returned

So our next test will ensure that any results are returned in the correct order.  As we’ve already refactored a lot of the code required for set up and preparation, writing this test couldn’t be easier. tSQLt will automatically run [ExceptionReaderTests].[SetUp] before any test in the [ExceptionReaderTests] class and this will create the [expected] and [actual] tables that we are going to use to validate our results. One thing to note about how these tables are created is that we use an IDENTITY column (LineItemId) with a clustered index on both [actual] and [expected] tables to ensure that the rows are stored in the the order in which they are added – since this is the only certain way we can evaluate the order in which the results are returned.

We create three Exception ID variables (we don’t need more than that to evaluate the display order) plus the UTC and System dates that would ordinarily be generated by the defaults on the Exception table. We use the same date/time values on all three rows so we can be certain that it is ExceptionId that is being used to order the results as all three rows are otherwise identical. We then call [tSQLt].[FakeTable] to mock the Exception table, thus isolating this test from the rest of the database. The last bit of setup we need to do makes use of the test helpers we talked about earlier, adding the three rows to the Exception table and also the [expected] table. One thing to note is that we actually process these in reverse ID order so that they are added to [expected] in the correct order: i.e. top row first. This only works if we specify the ID values manually rather than letting them auto-generate within [ExceptionBuilder].

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

    DECLARE @firstExceptionId int; SET @firstExceptionId = 1;
    DECLARE @nextExceptionId int; SET @nextExceptionId = 2;
    DECLARE @lastExceptionId int; SET @lastExceptionId = 3;

    DECLARE @UtcDate datetime; SET @UtcDate = GETUTCDATE();
    DECLARE @SystemDate datetime; SET @SystemDate = GETDATE();

    EXEC tSQLt.FakeTable 'log4.Exception';

    --! We have to make these based on the order in which we expect them to appear
    --! in the results. I.E. top row first (which only works if we specify the ID
    --! values manually rather than letting them auto-generate)
    EXEC @_ErrorNumber = [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
              @ExceptionId     = @lastExceptionId
            , @UtcDate         = @UtcDate
            , @SystemDate      = @SystemDate
            , @ErrorMessageOut = @_ErrorMessage OUT

    IF @_ErrorNumber = 0
        EXEC @_ErrorNumber = [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
                  @ExceptionId     = @nextExceptionId
                , @UtcDate         = @UtcDate
                , @SystemDate      = @SystemDate
                , @ErrorMessageOut = @_ErrorMessage OUT

    IF @_ErrorNumber = 0
        EXEC @_ErrorNumber = [ExceptionReaderTests].[Make_ExceptionReader_ExceptionRowAndResultRow]
                  @ExceptionId     = @firstExceptionId
                , @UtcDate         = @UtcDate
                , @SystemDate      = @SystemDate
                , @ErrorMessageOut = @_ErrorMessage OUT

    IF @_ErrorNumber > 0
        EXEC tSQLt.Fail @_ErrorMessage;

    INSERT [ExceptionReaderTests].[actual]
    EXEC log4.ExceptionReader;

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

Although we could just run this one test (as per the first call below), as we are still developing [ExceptionReader], it is better to run all the tests for that module as per the second call. This ensures that as we build up more complex functionality, we don’t break any of our earlier tests. So when we run all the tests in this class, we get the results shown below.

--! Run this test in isolation
EXEC tSQLt.Run '[TestExceptionReader].[Test_ExceptionReader_returns_results_most_recent_record_at_top]';

--! Run all ExceptionReader tests in this class
EXEC tSQLt.RunTestClass 'TestExceptionReader';

Looking at the output, we can see that Exception ID 2 came out in the middle of both result sets but that the actual (“<”) results were returned ordered [1] at the top and [3] at the bottom whereas we were expecting (“>”) the other way round.

[ExceptionReaderTests].[Test_ExceptionReader_returns_results_most_recent_record_at_top] failed: unexpected/missing resultset rows!
|_m_|LineItemId|ExceptionId|UtcDate                |SystemDate             |ErrorNumber|ErrorContext|ErrorMessage|ErrorSeverity|ErrorState|ErrorProcedure|ErrorLine|SessionId|ProgramName|NTDomain|NTUsername|LoginName|
+---+----------+-----------+-----------------------+-----------------------+-----------+------------+------------+-------------+----------+--------------+---------+---------+-----------+--------+----------+---------+
|<  |1         |3          |2011-12-06 01:56:22.170|2011-12-06 09:56:22.170|0          |            |            |0            |0         |              |0        |0        |           |        |          |         |
|<  |3         |1          |2011-12-06 01:56:22.170|2011-12-06 09:56:22.170|0          |            |            |0            |0         |              |0        |0        |           |        |          |         |
|=  |2         |2          |2011-12-06 01:56:22.170|2011-12-06 09:56:22.170|0          |            |            |0            |0         |              |0        |0        |           |        |          |         |
|>  |1         |1          |2011-12-06 01:56:22.170|2011-12-06 09:56:22.170|0          |            |            |0            |0         |              |0        |0        |           |        |          |         |
|>  |3         |3          |2011-12-06 01:56:22.170|2011-12-06 09:56:22.170|0          |            |            |0            |0         |              |0        |0        |           |        |          |         |

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

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

This is expected as we haven’t yet changed ExceptionReader to pass this new test, which by the way just needs this to pass:

    ORDER BY
        ExceptionId DESC

In this post we started writing our first few [ExceptionReader] tests, validating a simple SELECT statement and the order in which the results were returned. We also explored how the tSQLt automatic SetUp feature and the Test Data Builder pattern can help us refactor our tests so that we don’t have to write any more code than necessary. In Part 7, we will be looking at writing some tests for the WHERE clause and validating how default values are applied to some search predicates.

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 }

Barry July 2, 2012 at 07:21
Greg M Lucas July 2, 2012 at 08:20

Barry, thank you for taking the time to read this and provide feedback. I have corrected the broken link.

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: