USP_SALESORDER_MEMBERSHIP_ADDADDRESSES

            CREATE procedure dbo.USP_SALESORDER_MEMBERSHIP_ADDADDRESSES (
                @SALESORDERID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime
            )
            as
            begin
                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate();

                --Default: Address to use if primary member doesn't have an address to copy to the remaining members on a membership

                declare @DEFAULTADDRESSID uniqueidentifier;
                declare @DEFAULTADDRESSBLOCK nvarchar(150);
                declare @DEFAULTCOUNTRYID uniqueidentifier;
                declare @DEFAULTSTATEID uniqueidentifier = null;
                declare @DEFAULTCITY nvarchar(50);
                declare @DEFAULTPOSTCODE nvarchar(12);
                declare @DEFAULTDONOTMAIL bit;
                declare @DEFAULTSTARTDATE dbo.UDT_MONTHDAY;
                declare @DEFAULTENDDATE dbo.UDT_MONTHDAY;

                declare @SALESMETHODTYPECODE tinyint;
                declare @CONSTITUENTID uniqueidentifier;

                select
                    @SALESMETHODTYPECODE = SALESMETHODTYPECODE,
                    @CONSTITUENTID = CONSTITUENTID,
                    @DEFAULTADDRESSID = ADDRESSID
                from
                    dbo.SALESORDER
                where
                    ID = @SALESORDERID

                declare @ORIGINCODE tinyint = 0;

                if @SALESMETHODTYPECODE = 2  -- Online Sales

                    set @ORIGINCODE = 1;

                -- Branching based on parameter to get an optimal execution plan

                if @DEFAULTADDRESSID is not null begin
                    select
                        @DEFAULTADDRESSID = ID,
                        @DEFAULTADDRESSBLOCK = ADDRESSBLOCK,
                        @DEFAULTCOUNTRYID = COUNTRYID,
                        @DEFAULTSTATEID = STATEID,
                        @DEFAULTCITY = CITY,
                        @DEFAULTPOSTCODE = POSTCODE,
                        @DEFAULTDONOTMAIL = DONOTMAIL,
                        @DEFAULTSTARTDATE = STARTDATE,
                        @DEFAULTENDDATE = ENDDATE
                    from
                        dbo.ADDRESS
                    where
                        ID = @DEFAULTADDRESSID
                end
                else begin
                    select top 1
                        @DEFAULTADDRESSID = ID,
                        @DEFAULTADDRESSBLOCK = ADDRESSBLOCK,
                        @DEFAULTCOUNTRYID = COUNTRYID,
                        @DEFAULTSTATEID = STATEID,
                        @DEFAULTCITY = CITY,
                        @DEFAULTPOSTCODE = POSTCODE,
                        @DEFAULTDONOTMAIL = DONOTMAIL,
                        @DEFAULTSTARTDATE = STARTDATE,
                        @DEFAULTENDDATE = ENDDATE
                    from
                        dbo.ADDRESS
                    where
                        CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1
                end

                declare @MEMBERSHIPID uniqueidentifier;
                declare @MEMBERCONSTITUENTID uniqueidentifier;
                --Primary: primary member address

                declare @PRIMARYADDRESSID uniqueidentifier;
                declare @PRIMARYADDRESSBLOCK nvarchar(150);
                declare @PRIMARYCOUNTRYID uniqueidentifier;
                declare @PRIMARYSTATEID uniqueidentifier = null;
                declare @PRIMARYCITY nvarchar(50);
                declare @PRIMARYPOSTCODE nvarchar(12);
                declare @PRIMARYDONOTMAIL bit;
                declare @PRIMARYSTARTDATE dbo.UDT_MONTHDAY;
                declare @PRIMARYENDDATE dbo.UDT_MONTHDAY;

                declare MEMBERSHIP_CURSOR cursor local fast_forward for
                select [MEMBERSHIPID]
                from dbo.[SALESORDERITEMMEMBERSHIP]
                inner join dbo.[SALESORDERITEM]
                    on [SALESORDERITEMMEMBERSHIP].[ID] = [SALESORDERITEM].[ID]
                where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID;

                open MEMBERSHIP_CURSOR
                fetch next from MEMBERSHIP_CURSOR into @MEMBERSHIPID
                while @@FETCH_STATUS = 0
                begin
                    select top 1 
                        @PRIMARYADDRESSID = [ADDRESS].[ID],
                        @PRIMARYADDRESSBLOCK = [ADDRESS].[ADDRESSBLOCK],
                        @PRIMARYCOUNTRYID = [ADDRESS].[COUNTRYID],
                        @PRIMARYSTATEID = [ADDRESS].[STATEID],
                        @PRIMARYCITY = [ADDRESS].[CITY],
                        @PRIMARYPOSTCODE = [ADDRESS].[POSTCODE],
                        @PRIMARYDONOTMAIL = [ADDRESS].[DONOTMAIL],
                        @PRIMARYSTARTDATE = [ADDRESS].[STARTDATE],
                        @PRIMARYENDDATE = [ADDRESS].[ENDDATE]
                    from dbo.[ADDRESS]
                    inner join dbo.[MEMBER]
                        on [ADDRESS].[CONSTITUENTID] = [MEMBER].[CONSTITUENTID]
                    inner join dbo.[MEMBERSHIP]
                        on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
                    where 
                        [ADDRESS].[ISPRIMARY] = 1 and
                        [MEMBERSHIP].[ID] = @MEMBERSHIPID and
                        [MEMBER].[ISPRIMARY] = 1 and
                        [MEMBER].[ISDROPPED] = 0;

                    if @PRIMARYADDRESSID is not null or @DEFAULTADDRESSID is not null
                    begin
                        declare MEMBERS_CURSOR cursor local fast_forward for
                        select [SALESORDERITEMMEMBER].[CONSTITUENTID]
                        from dbo.[SALESORDERITEMMEMBER]
                        inner join dbo.[SALESORDERITEMMEMBERSHIP]
                            on [SALESORDERITEMMEMBER].[SALESORDERITEMMEMBERSHIPID] = [SALESORDERITEMMEMBERSHIP].[ID]
                        inner join dbo.[SALESORDERITEM]
                            on [SALESORDERITEMMEMBERSHIP].[ID] = [SALESORDERITEM].[ID]
                        where 
                            [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                            not exists (select 1 from dbo.[ADDRESS] where [CONSTITUENTID] = [SALESORDERITEMMEMBER].[CONSTITUENTID])

                        --Copy: address to copy; setting here so it doesn't have to be figured out inside the loop

                        declare @COPYADDRESSBLOCK nvarchar(150);
                        declare @COPYCOUNTRYID uniqueidentifier;
                        declare @COPYSTATEID uniqueidentifier = null;
                        declare @COPYCITY nvarchar(50);
                        declare @COPYPOSTCODE nvarchar(12);
                        declare @COPYDONOTMAIL bit;
                        declare @COPYSTARTDATE dbo.UDT_MONTHDAY;
                        declare @COPYENDDATE dbo.UDT_MONTHDAY;
                        if @PRIMARYADDRESSID is not null
                        begin
                            select
                                @COPYADDRESSBLOCK = @PRIMARYADDRESSBLOCK,
                                @COPYCOUNTRYID = @PRIMARYCOUNTRYID,
                                @COPYSTATEID = @PRIMARYSTATEID,
                                @COPYCITY = @PRIMARYCITY,
                                @COPYPOSTCODE = @PRIMARYPOSTCODE,
                                @COPYDONOTMAIL = @PRIMARYDONOTMAIL,
                                @COPYSTARTDATE = @PRIMARYSTARTDATE,
                                @COPYENDDATE = @PRIMARYENDDATE
                        end
                        else
                        begin
                            select
                                @COPYADDRESSBLOCK = @DEFAULTADDRESSBLOCK,
                                @COPYCOUNTRYID = @DEFAULTCOUNTRYID,
                                @COPYSTATEID = @DEFAULTSTATEID,
                                @COPYCITY = @DEFAULTCITY,
                                @COPYPOSTCODE = @DEFAULTPOSTCODE,
                                @COPYDONOTMAIL = @DEFAULTDONOTMAIL,
                                @COPYSTARTDATE = @DEFAULTSTARTDATE,
                                @COPYENDDATE = @DEFAULTENDDATE
                        end

                        open MEMBERS_CURSOR
                        fetch next from MEMBERS_CURSOR into @MEMBERCONSTITUENTID;
                        while  @@FETCH_STATUS = 0
                        begin
                            exec dbo.USP_ADDRESS_CREATE
                                null, --ID

                                @CHANGEAGENTID,
                                @MEMBERCONSTITUENTID,
                                null, --ADDRESSTYPECODEID

                                1, --Primary

                                @COPYDONOTMAIL,
                                @COPYSTARTDATE,
                                @COPYENDDATE,
                                @COPYCOUNTRYID,
                                @COPYSTATEID,
                                @COPYADDRESSBLOCK,
                                @COPYCITY,
                                @COPYPOSTCODE,
                                @ORIGINCODE = @ORIGINCODE;

                            fetch next from MEMBERS_CURSOR into @MEMBERCONSTITUENTID;
                        end
                        close MEMBERS_CURSOR
                        deallocate MEMBERS_CURSOR
                    end

                    fetch next from MEMBERSHIP_CURSOR into @MEMBERSHIPID
                end
                close MEMBERSHIP_CURSOR
                deallocate MEMBERSHIP_CURSOR
            end