IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HDOSYA_ICD_LISTE_ONTANI_ICDKOD]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION  [dbo].[HDOSYA_ICD_LISTE_ONTANI_ICDKOD]
go


CREATE FUNCTION [dbo].[HDOSYA_ICD_LISTE_ONTANI_ICDKOD] (@HDosyaId as Integer)  
RETURNS nvarchar(1000) AS  
BEGIN
declare @ICDListe as nvarchar(1000),
      @ICDTURU as nvarchar(10),
      @ICDKOD as nvarchar(9),
      @ICDADI as NVARCHAR(100)
SET @ICDListe = ''
DECLARE HDOSYA_ICD_CURSOR CURSOR
   FOR SELECT ICDTURU,HDOSYAICD.ICDKOD, ICDADI FROM HDOSYAICD (NOLOCK) INNER JOIN TICD (NOLOCK) ON HDOSYAICD.ICDKOD = TICD.ICDKOD INNER JOIN TICDTURU ON HDOSYAICD.ICDTURUID=TICDTURU.ICDTURUID
    WHERE HDOSYAICD.ICDTURUID=1  AND HDOSYAID = @HDosyaId
OPEN HDOSYA_ICD_CURSOR
FETCH NEXT FROM HDOSYA_ICD_CURSOR INTO @ICDTURU, @ICDKOD, @ICDADI
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @ICDListe = @ICDListe + @ICDKOD  + ', '
      FETCH NEXT FROM HDOSYA_ICD_CURSOR INTO @ICDTURU,@ICDKOD, @ICDADI
END
      IF CHARINDEX(',', ISNULL(@ICDListe,' ')) <> 0
            SET @ICDListe = LEFT(@ICDListe, LEN(@ICDListe)-1)
return @ICDListe
END



GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HDOSYA_ICD_LISTE_KESINTANI_ICDKOD]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION  [dbo].[HDOSYA_ICD_LISTE_KESINTANI_ICDKOD]
go


CREATE FUNCTION [dbo].[HDOSYA_ICD_LISTE_KESINTANI_ICDKOD] (@HDosyaId as Integer)  
RETURNS nvarchar(1000) AS  
BEGIN
declare @ICDListe as nvarchar(1000),
      @ICDTURU as nvarchar(10),
      @ICDKOD as nvarchar(9),
      @ICDADI as NVARCHAR(100)
SET @ICDListe = ''
DECLARE HDOSYA_ICD_CURSOR CURSOR
   FOR SELECT ICDTURU,HDOSYAICD.ICDKOD, ICDADI FROM HDOSYAICD (NOLOCK) INNER JOIN TICD (NOLOCK) ON HDOSYAICD.ICDKOD = TICD.ICDKOD INNER JOIN TICDTURU ON HDOSYAICD.ICDTURUID=TICDTURU.ICDTURUID
    WHERE HDOSYAICD.ICDTURUID=2  AND HDOSYAID = @HDosyaId
OPEN HDOSYA_ICD_CURSOR
FETCH NEXT FROM HDOSYA_ICD_CURSOR INTO @ICDTURU, @ICDKOD, @ICDADI
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @ICDListe = @ICDListe +  @ICDKOD  +  ', '
      FETCH NEXT FROM HDOSYA_ICD_CURSOR INTO @ICDTURU,@ICDKOD, @ICDADI
END
      IF CHARINDEX(',', ISNULL(@ICDListe,' ')) <> 0
            SET @ICDListe = LEFT(@ICDListe, LEN(@ICDListe)-1)
return @ICDListe
END