utility_ut.insert_state

use [chamomile];

go

if object_id(N'[utility_ut].[insert_state]',
             N'P') is not null
    drop procedure [utility_ut].[insert_state];

go

/*
    declare @test_stack [xml];
    execute [utility_ut].[insert_state] @test_stack=@test_stack output;
    select @test_stack;
*/
create procedure [utility_ut].[insert_state]
    @test_stack [xml] output
as
    begin
        declare @test    [xml],
                @message [nvarchar](max),
                @count   [int];

        set @test_stack = N'<object_stack stack_type="result" test_count="-1" pass_count="-1" timestamp="'
                          + convert([sysname], current_timestamp, 126)
                          + N'" />';

        begin
            begin transaction;

            ---------------------------------------------------------------------------------------------
            -- test 1 - no country
            begin
                set @test = N'<object object_type="test" name="" result="fail" />';
                set @test.modify(N'replace value of (/object/@name)[1] with ("test 1 - no country")');

                begin try
                    execute [utility].[insert_state]
                        @state       =N'newstate',
                        @abbreviation=N'ns';
                end try

                begin catch
                    set @test.modify(N'replace value of (/object/@result)[1] with ("pass")');
                    set @message = error_message();
                    set @test.modify(N'insert text {sql:variable("@message")} as first into (/object)[1]');
                end catch

                set @test_stack.modify(N'insert sql:variable("@test") as last into (/object_stack)[1]');
            end

            ---------------------------------------------------------------------------------------------
            -- test 2 - no state
            begin
                set @test = N'<object object_type="test" name="" result="fail" />';
                set @test.modify(N'replace value of (/object/@name)[1] with ("test 2 - no state")');

                begin try
                    execute [utility].[insert_state]
                        @country     =N'newcountry',
                        @abbreviation=N'ns';
                end try

                begin catch
                    set @test.modify(N'replace value of (/object/@result)[1] with ("pass")');
                    set @message = error_message();
                    set @test.modify(N'insert text {sql:variable("@message")} as first into (/object)[1]');
                end catch

                set @test_stack.modify(N'insert sql:variable("@test") as last into (/object_stack)[1]');
            end

            ---------------------------------------------------------------------------------------------
            -- test 3 - no abbreviation
            begin
                set @test = N'<object object_type="test" name="" result="fail" />';
                set @test.modify(N'replace value of (/object/@name)[1] with ("test 3 - no abbreviation")');

                begin try
                    execute [utility].[insert_state]
                        @country =N'newcountry',
                        @state   =N'newstate';
                end try

                begin catch
                    set @test.modify(N'replace value of (/object/@result)[1] with ("pass")');
                    set @message = error_message();
                    set @test.modify(N'insert text {sql:variable("@message")} as first into (/object)[1]');
                end catch

                set @test_stack.modify(N'insert sql:variable("@test") as last into (/object_stack)[1]');
            end

            ---------------------------------------------------------------------------------------------
            -- test 4 - new entry
            begin
                set @test = N'<object object_type="test" name="" result="fail" />';
                set @test.modify(N'replace value of (/object/@name)[1] with ("test 4 - new entry")');

                execute [utility].[insert_state]
                    @country     =N'newcountry',
                    @state       =N'newstate',
                    @abbreviation=N'ns';

                if (select [utility].[get_state_abbreviation] (null,
                                                               N'newcountry',
                                                               N'newstate')) = N'ns'
                    set @test.modify(N'replace value of (/object/@result)[1] with ("pass")');

                set @test_stack.modify(N'insert sql:variable("@test") as last into (/object_stack)[1]');
            end

            ---------------------------------------------------------------------------------------------
            -- test 5 - existing entry
            begin
                set @test = N'<object object_type="test" name="" result="fail" />';
                set @test.modify(N'replace value of (/object/@name)[1] with ("test 5 - existing entry")');

                begin try
                    set @count = (select count(*)
                                  from   [utility].[state]);

                    execute [utility].[insert_state]
                        @country     =N'usa',
                        @state       =N'texas',
                        @abbreviation=N'tx';

                    if (select count(*)
                        from   [utility].[state]) = @count
                        set @test.modify(N'replace value of (/object/@result)[1] with ("pass")');
                end try

                begin catch
                    set @message = error_message();
                    set @test.modify(N'insert text {sql:variable("@message")} as first into (/object)[1]');
                end catch

                set @test_stack.modify(N'insert sql:variable("@test") as last into (/object_stack)[1]');
            end

            rollback;
        end

        ---------------------------------------------------------------------------------------------
        -- format test_stack
        begin
            set @count = @test_stack.value(N'count (/object_stack/object[@object_type="test"])',
                                           'int');
            set @test_stack.modify(N'replace value of (/object_stack/@test_count)[1] with sql:variable("@count")');
            set @count = @test_stack.value(N'count (/object_stack/object[@object_type="test"][@result="pass"])',
                                           'int');
            set @test_stack.modify(N'replace value of (/object_stack/@pass_count)[1] with sql:variable("@count")');
        end
    end

go 

[test_driven_development_example]

---

copyright Katherine Elizabeth Lightsey 1959-2013 (aka; my life)

"A person with a new idea is a crank until the idea succeeds." - Mark Twain