DECLARE
  @v_result nvarchar(2000)
  cursor name_cur IS
    SELECT a.COL_FIRSTNAME AS FIRSTNAME,
      a.COL_LASTNAME AS LASTNAME,
      a.COL_MIDDLENAME AS MIDDLENAME,
      a.COL_TITLE AS TITLE,
      nvl(dc.col_value, a.COL_SUFFIX) AS SUFFIX,
      a.COL_BUSNAME AS BUSNAME,
      a.COL_TYPE AS TYPE
    FROM tbl_party a
      LEFT OUTER JOIN TBL_DICTIONARY dc ON dc.col_code = a.COL_SUFFIX AND dc.col_category = 'SUFFIX'
    WHERE a.COL_ID = PartyId;

  --v_name name_cur%rowtype;

 DECLARE @TYPE VARCHAR(55);
 DECLARE @TITLE VARCHAR(55);
 DECLARE @FIRSTNAME VARCHAR(55);
 DECLARE @MIDDLENAME VARCHAR(55);
 DECLARE @LASTNAME VARCHAR(55);
 DECLARE @LASTNAME VARCHAR(55);
 DECLARE @SUFFIX VARCHAR(55);
 DECLARE @BUSNAME VARCHAR(55);
BEGIN
    SET @v_result = '';
  /*
  open name_cur;
  fetch name_cur into v_name;
  if name_cur%NOTFOUND then
    close name_cur;
    RAISE NO_DATA_FOUND;
  else
    close name_cur;
  end if;
  */
  -- if fields set, then add it to result
  IF (UPPER(@TYPE) != 'BUSINESS')
    IF (@TITLE IS NOT NULL)
      SET @v_result := @v_result + @TITLE + ' ';

    IF (@FIRSTNAME IS NOT NULL)
      SET @v_result := @v_result + @FIRSTNAME;

    IF(@v_result IS NOT NULL AND @MIDDLENAME IS NOT NULL)
     SET @v_result := @v_result + ' ';

    IF (@MIDDLENAME IS NOT NULL)
      SET @v_result := @v_result + SUBSTR(@MIDDLENAME, 1, 1) + '.';

    IF(@v_result IS NOT NULL AND @LASTNAME IS NOT NULL)
      SET @v_result := @v_result + ' ';

    IF (@LASTNAME IS NOT NULL)
      SET @v_result := @v_result + @LASTNAME;

    IF(@v_result IS NOT NULL AND @SUFFIX IS NOT NULL)
      SET @v_result := @v_result + ', ' ;

    IF (@SUFFIX IS NOT NULL)
      @v_result := @v_result +  @v_name.SUFFIX;

  IF (UPPER(@TYPE) = 'BUSINESS')
    IF (@BUSNAME IS NOT NULL)
      @v_result := @v_result + @BUSNAME;

  /*return v_result;*/
END;