UFN_BATCHSPONSORSHIPCONSTITUENTACCOUNT_GETDESCRIPTION

            create function dbo.UFN_BATCHSPONSORSHIPCONSTITUENTACCOUNT_GETDESCRIPTION(@ID uniqueidentifier)
            returns nvarchar(254)
            with execute as caller
            as begin
                    declare @INSTITUTIONNAME nvarchar(202);
                    declare @ACCOUNTNUMBER nvarchar(50);

                    select
                        @INSTITUTIONNAME = FINANCIALINSTITUTION.DESCRIPTION,
                        @ACCOUNTNUMBER = coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),'')
                    from
                        dbo.CONSTITUENTACCOUNT
                    inner join
                        dbo.FINANCIALINSTITUTION
                    on
                        CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = FINANCIALINSTITUTION.ID
                    where CONSTITUENTACCOUNT.ID = @ID;

                    -- If @INSTITUTIONNAME and @ACCOUNTNUMBER are null, try BATCHSPONSORSHIPCONSTITUENTACCOUNT

                    if @INSTITUTIONNAME is null and @ACCOUNTNUMBER is null
                        select
                            @INSTITUTIONNAME = FINANCIALINSTITUTION.DESCRIPTION,
                            @ACCOUNTNUMBER = coalesce(convert(nvarchar(50), DecryptByKey(BATCHSPONSORSHIPCONSTITUENTACCOUNT.ACCOUNTNUMBER)),'')
                        from
                            dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT
                        inner join
                            dbo.FINANCIALINSTITUTION
                        on
                            BATCHSPONSORSHIPCONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = FINANCIALINSTITUTION.ID
                        where BATCHSPONSORSHIPCONSTITUENTACCOUNT.ID = @ID;

                    if @INSTITUTIONNAME is null and @ACCOUNTNUMBER is null
                        return '';

                    if len(@ACCOUNTNUMBER) = 0 
                        return @INSTITUTIONNAME;
                    else
                        return @ACCOUNTNUMBER + ' (' + @INSTITUTIONNAME + ')';

                    return '';
            end