UFN_BATCHREVENUECONSTITUENTACCOUNT_GETDESCRIPTION

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

  if exists(select ID from dbo.CONSTITUENTACCOUNT where ID = @ID)
  begin

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

  end
  else
  begin

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

  end

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

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

  return '';
end