USP_BBNC_CONSTITPHONEOREMAIL_FOR_GIVEN_CONSTITIDS

CREATE procedure [dbo].[USP_BBNC_CONSTITPHONEOREMAIL_FOR_GIVEN_CONSTITIDS] (
    @IDs nvarchar(max)
) as begin
    set nocount on;

    declare @IDS_TABLE table (
        SEQUENCEID int,
        CONSTITUENTID uniqueidentifier
    );

    insert into
        @IDS_TABLE (SEQUENCEID)
    select
        ID
    from
        dbo.fnMakeIDsTableFromString(@IDs, ',');

    update
        @IDS_TABLE
    set
        CONSTITUENTID = C.ID
    from
        @IDS_TABLE IDT
    inner join
        dbo.CONSTITUENT C on C.SEQUENCEID = IDT.SEQUENCEID;

    select
        coalesce(EMAILADDRESSTYPECODE.DESCRIPTION, '') as [PHONETYPE],
        EMAILADDRESS.EMAILADDRESS as [NUMBER],
        cast(1 as bit) as [ISEMAIL],
        EMAILADDRESS.ISPRIMARY,
        coalesce(dbo.[UFN_BBNC_GETCODETABLEMAPID] ('E0473233-93ED-4509-9C42-65E037405497', EMAILADDRESS.EMAILADDRESSTYPECODEID), 0) as SYSTEMID,
        CONSTITUENT.SEQUENCEID
    from
        dbo.CONSTITUENT
        inner join dbo.EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID
        inner join @IDS_TABLE IDs on IDS.CONSTITUENTID = EMAILADDRESS.CONSTITUENTID
        left join dbo.EMAILADDRESSTYPECODE on EMAILADDRESS.EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODE.ID

    union all

    select
        PHONETYPECODE.DESCRIPTION as [PHONETYPE],
        PHONE.NUMBER,
        cast(0 as bit) as [ISEMAIL],
        PHONE.ISPRIMARY,
        BBNCCODETABLEIDMAP.ID as SYSTEMID,
        CONSTITUENT.SEQUENCEID
    from
        dbo.CONSTITUENT
        inner join dbo.PHONE on CONSTITUENT.ID = PHONE.CONSTITUENTID
        inner join dbo.PHONETYPECODE on PHONE.PHONETYPECODEID = PHONETYPECODE.ID
        inner join dbo.BBNCCODETABLEIDMAP on
            PHONE.PHONETYPECODEID = BBNCCODETABLEIDMAP.TABLEENTRYID
            and 'D57063E8-9DEA-4651-90A7-F86FE010AFC3' = BBNCCODETABLEIDMAP.CODETABLECATALOGID
        inner join 
            @IDS_TABLE IDs on IDs.CONSTITUENTID = PHONE.CONSTITUENTID

    union all

    select 
        'WebAddress' as [PHONETYPE],
        CONSTITUENT.WEBADDRESS,
        cast(0 as bit) as [ISEMAIL],
        cast(1 as bit) as [ISPRIMARY],
        0 as SYSTEMID,
        CONSTITUENT.SEQUENCEID
    from 
        dbo.CONSTITUENT
    inner join 
        @IDS_TABLE IDs on IDs.CONSTITUENTID = CONSTITUENT.ID
end