USP_SIMPLEDATALIST_SPONSORSHIPBATCHCONSTITUENTACCOUNT

create procedure dbo.USP_SIMPLEDATALIST_SPONSORSHIPBATCHCONSTITUENTACCOUNT
(
    @CONSTITUENTID uniqueidentifier = null
)
as
    set nocount on;

    -- Open the symmetric key for decryption

    exec dbo.USP_GET_KEY_ACCESS;

    -- Use either CONSTITUENTACCOUNT or SPONSORSHIPBATCHCONSTITUENTACCOUNT depending

    -- on whether the constituent exists or not

    if exists (select 1 from dbo.CONSTITUENT where ID = @CONSTITUENTID)
    begin
        select 
            CONSTITUENTACCOUNT.ID as VALUE,
            CONSTITUENTACCOUNT.DESCRIPTION as LABEL
        from
            dbo.CONSTITUENTACCOUNT
        inner join
            dbo.FINANCIALINSTITUTION
        on
            CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = FINANCIALINSTITUTION.ID
        where
            CONSTITUENTID = @CONSTITUENTID
        order by
            CONSTITUENTACCOUNT.DATEADDED;
    end
    else
    begin
        select 
            BATCHSPONSORSHIPCONSTITUENTACCOUNT.ID as VALUE,
            BATCHSPONSORSHIPCONSTITUENTACCOUNT.DESCRIPTION as LABEL
        from
            dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT
        inner join
            dbo.FINANCIALINSTITUTION
        on
            BATCHSPONSORSHIPCONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = FINANCIALINSTITUTION.ID
        where
            CONSTITUENTID = @CONSTITUENTID
        order by
            BATCHSPONSORSHIPCONSTITUENTACCOUNT.DATEADDED;                
    end

    close symmetric key sym_BBInfinity;