utility.insert_state

use [chamomile];

go

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

go

create procedure [utility].[insert_state]
    @country      [nvarchar](5),
    @state        [sysname],
    @abbreviation [char](2)
as
    begin
        declare @keep as varchar(50) = '%[^a-z ]%';

        set @country=lower(@country);
        set @state = lower(@state);
        set @abbreviation = lower(@abbreviation);

        -------------------------------------------------------------------------------------------
        -- Remove all non-alphabet characters from inputs
        begin
            while patindex(@keep,
                           @country) > 0
                set @country = stuff(@country,
                                     patindex(@keep,
                                              @country),
                                     1,
                                     '');

            while patindex(@keep,
                           @state) > 0
                set @state = stuff(@state,
                                   patindex(@keep,
                                            @state),
                                   1,
                                   '');
        end;

        -------------------------------------------------------------------------------------------
        -- Merge into table
        merge into [utility_secure].[state] as target
        using (values(lower(@country),
              lower(@state),
              lower(@abbreviation))) as source ([country], [state], [abbreviation])
        on target.[country] = source.[country]
           and target.[state] = source.[state]
           and target.[abbreviation] = source.[abbreviation]
        when not matched by target then
            insert ([country],
                    [state],
                    [abbreviation])
            values ([country],
                    [state],
                    [abbreviation]);
    end;

go 

[test_driven_development_example]

---

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

"Anything that just costs money is cheap." - John Steinbeck