--SELECT dbo.func_get_keterangan_klaim_ref_detail('2012.256611')

CREATE FUNCTION dbo.func_get_keterangan_klaim_ref_detail (
  @pi_no_klaim varchar(11)
)
RETURNS varchar(4000)
AS
BEGIN
  DECLARE
    @p_keterangan varchar(200),
    @p_keterangan_gabungan varchar(4000)

  SET @p_keterangan_gabungan = ''

  DECLARE keterangan_cursor CURSOR FOR
  SELECT 
    Keterangan
  FROM 
    Klaim_Ref_Detail krd
  WHERE 
    (No_Klaim = @pi_no_klaim) AND
    (COALESCE(LTRIM(RTRIM(Keterangan)), '') <> '')
  ORDER BY
    Kode_Santunan

  OPEN keterangan_cursor
  FETCH NEXT FROM keterangan_cursor INTO @p_keterangan

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @p_keterangan_gabungan <> ''
    BEGIN 
      SET @p_keterangan_gabungan = @p_keterangan_gabungan + ', '
    END

    SET @p_keterangan_gabungan = @p_keterangan_gabungan + @p_keterangan

    FETCH NEXT FROM keterangan_cursor INTO @p_keterangan
  END

  CLOSE keterangan_cursor
  DEALLOCATE keterangan_cursor

  RETURN (@p_keterangan_gabungan)
END
