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

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


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



GO


