USP_ADDRESSPROPERCASEEXCEPTION_ADDDEFAULTWORDS

create procedure dbo.USP_ADDRESSPROPERCASEEXCEPTION_ADDDEFAULTWORDS
(
    @CHANGEAGENTID uniqueidentifier = null
)
with execute as caller
as
begin
    set nocount on;

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    declare @DEFAULTWORD table
    (
        WORD nvarchar(100)
    );

    begin try

        insert into @DEFAULTWORD(WORD)
        values
            ('A.F.B.'),
            ('A.P.O.'),
            ('AFB'),
            ('AFRLPR'),
            ('APO'),
            ('BB'),
            ('c/o'),
            ('D.P.O.'),
            ('DPO'),
            ('EE'),
            ('F.P.O.'),
            ('FF'),
            ('FPO'),
            ('G.P.O.'),
            ('GPO'),
            ('H.C.'),
            ('H.O.B.'),
            ('HC'),
            ('HOB'),
            ('I.I.'),
            ('I.I.I.'),
            ('I.V.'),
            ('II'),
            ('III'),
            ('IV'),
            ('LaSalle'),
            ('LL'),
            ('LLP'),
            ('Macclesfield'), 
            ('Macdill'),
            ('Macedonia'), 
            ('Mackinac'), 
            ('Mackinaw'), 
            ('Macomb'), 
            ('Macon'), 
            ('Macy'), 
            ('Macungie'),
            ('Macero'),
            ('N.E.'),
            ('N.W.'),
            ('NE'),
            ('NW'),
            ('P.O.'),
            ('P.S.C.'),
            ('PO'),
            ('PSC'),
            ('R.F.D.'),
            ('R.R.'),
            ('RFD'),
            ('RR'),
            ('S.E.'),
            ('S.W.'),
            ('SE'),
            ('SW'),
            ('U.S.A.F.'),
            ('UCLA'),
            ('US'),
            ('USA'),
            ('USAF'),
            ('V.I.'),
            ('V.I.I.'),
            ('V.I.I.I.'),
            ('VI'),
            ('VII'),
            ('VIII'),
            ('WPAFB');

        merge into dbo.ADDRESSPROPERCASEEXCEPTION
        using @DEFAULTWORD as DEFAULTWORD
        on DEFAULTWORD.WORD = ADDRESSPROPERCASEEXCEPTION.WORD
        when not matched then
            insert(ID,WORD,ENABLED,DATEADDED,DATECHANGED,ADDEDBYID,CHANGEDBYID)
            values(newid(),DEFAULTWORD.WORD,1,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID);

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch
end