USP_DATALIST_CONSTITUENTACCOUNT

CREATE procedure dbo.USP_DATALIST_CONSTITUENTACCOUNT
(
    @CONSTITUENTID uniqueidentifier = null
)
as
    set nocount on;

    -- Open the symmetric key for decryption

    exec dbo.USP_GET_KEY_ACCESS;

    select
        [ACCOUNT].ID,
        [FI].ID FINANCIALINSTITUTIONID,
        [FI].DESCRIPTION FINANCIALINSTITUTION,
        [FI].ROUTINGNUMBER,
        case when LEN(coalesce(convert(nvarchar(50), DecryptByKey([ACCOUNT].ACCOUNTNUMBER)),'')) = 0 then ''  
        else REPLICATE('x', LEN(coalesce(convert(nvarchar(50), DecryptByKey([ACCOUNT].ACCOUNTNUMBER)),''))- LEN(RIGHT(coalesce(convert(nvarchar(50), DecryptByKey([ACCOUNT].ACCOUNTNUMBER)),''),4))) + RIGHT(coalesce(convert(nvarchar(50), DecryptByKey([ACCOUNT].ACCOUNTNUMBER)),''),4) end ACCOUNTNUMBER,
        [ACCOUNT].ACCOUNTTYPE,
        [ACCOUNT].EFTSTATUS,
        [FI].SORTCODE,
        [ACCOUNT].ACCOUNTNAME NAMEONACCOUNT,
        BANKINGSYSTEM.NAME BANKINGSYSTEMNAME,
        BANKINGSYSTEM.CONDITIONSETTINGNAME BANKINGSYSTEMCONDITIONSETTINGNAME,
        [FI].BIC,
        [FI].BANKCODE
    from
        dbo.CONSTITUENTACCOUNT as [ACCOUNT]
        inner join dbo.FINANCIALINSTITUTION as [FI] on [FI].ID = [ACCOUNT].FINANCIALINSTITUTIONID
        left join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = [FI].BANKINGSYSTEMID
    where
        [ACCOUNT].CONSTITUENTID = @CONSTITUENTID
    order by
        [ACCOUNT].DATEADDED;

    close symmetric key sym_BBInfinity;