The final versions of the example SQL code written during my live-coding Test-Driven SQL talk

Test-Driven-SQL-Procedure.sql

if (object_id('pub.ReportBugsPerDeveloper') is not null)
    drop procedure pub.ReportBugsPerDeveloper;
go

create procedure pub.ReportBugsPerDeveloper
as
    select
            su.FirstName + ' ' + su.LastName    as FullName,
            count(b.BugId)                      as BugCount
    from
            dbo.SystemUser su
    left outer join dbo.Bug b
    on      b.AssignedUserId = su.UserId
    group by
            su.FirstName + ' ' + su.LastName
go

Test-Driven-SQL-Unit-Tests.sql

exec ssunit.TestSchema_Clear;
go

create procedure test._@FixtureSetup@_$Report$_
as
    create table test.Actual
    (
        FullName    varchar(100)    not null,
        BugCount    int             not null,
    );
go

create procedure test._@TestSetup@_$Report$_
as
    truncate table test.Actual;
    exec test_help.DeleteAll;
go

create procedure test._@Test@_$Report$_returns_developers_full_name
as
    declare @resolved pub.BugStatus_t = pub.BugStatus_Resolved();

    exec test_help.InsertUser 1, 'oldwoodc', 'chris', 'oldwood';
    exec test_help.InsertBug 11, 'summary', 1, @resolved, 1;

    insert into test.Actual
    exec pub.ReportBugsPerDeveloper

    declare @fullName varchar(100);
    select  @fullName = FullName
    from    test.Actual;

    exec ssunit.AssertStringEqualTo 'chris oldwood', @fullName;
go

create procedure test._@Test@_$Report$_returns_bug_count_per_developer
as
    declare @resolved pub.BugStatus_t = pub.BugStatus_Resolved();

    exec test_help.InsertUser 1, 'oldwoodc', 'chris', 'oldwood';
    exec test_help.InsertBug 11, 'summary', 1, @resolved, 1;

    insert into test.Actual
    exec pub.ReportBugsPerDeveloper

    declare @bugCount varchar(100);
    select  @bugCount = BugCount
    from    test.Actual;

    exec ssunit.AssertIntegerEqualTo 1, @bugCount;
go

create procedure test._@Test@_$Report$_returns_bug_count_even_when_developer_has_none_assigned
as
    exec test_help.InsertUser 1, 'oldwoodc', 'chris', 'oldwood';

    insert into test.Actual
    exec pub.ReportBugsPerDeveloper;

    insert into test.Expected(FullName,        BugCount)
                       select 'chris oldwood', 0

    exec ssunit.AssertTableEqualTo 'test.Expected', 'test.Actual';
go

exec ssunit.RunTests;
go

In my talk about Test-Driven SQL I do a live coding demo. The demo is to create a simple report style stored procedure with some unit tests, in a test-first manner. The code above is the resulting SQL unit tests and production code.

http://www.youtube.com/watch?v=5-MWYKLM3r0