IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Fn_Satinalma_FaturaListesi]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[Fn_Satinalma_FaturaListesi]
go

CREATE FUNCTION [dbo].[Fn_Satinalma_FaturaListesi] (@SATINALMAISLID as Integer)  
RETURNS nvarchar(500) AS  
BEGIN 
declare @FLISTE as nvarchar(500),
	@FATURA as nvarchar(100)
SET @FLISTE = '' 
DECLARE SATINALMA_FATURA_CURSOR CURSOR
   FOR  SELECT DISTINCT  'Tarih:'+CONVERT(NVARCHAR(15),TARIH,104)+' FatNo:'+ISNULL(FATNO,'')   
    FROM  
    (  
     SELECT FATURA.FATNO,FATURA.TARIH  
     FROM FATISL WITH(NOLOCK)  
       INNER JOIN FATURA WITH(NOLOCK) ON FATURA.FATID = FATISL.FATID  
     WHERE(ISNULL(FATURA.IRSALIYE, 0) = 0)  AND (ISNULL(FATURA.FIPTAL, 0) = 0)
       AND (FATISL.SATINALMAISLID = @SATINALMAISLID)  
     UNION ALL  
     SELECT FATURA.FATNO,FATURA.TARIH 
     FROM FATISL WITH(NOLOCK)  
       INNER JOIN FATURA WITH(NOLOCK) ON FATURA.FATID = FATISL.FATID  
       INNER JOIN FATISL AS F2 WITH(NOLOCK) ON F2.FATISLID = FATISL.IRSISLID  
     WHERE(ISNULL(FATURA.IRSALIYE, 0) = 0)  
       AND (F2.SATINALMAISLID = @SATINALMAISLID)  AND (ISNULL(FATURA.FIPTAL, 0) = 0)
    ) AS TBL 

OPEN SATINALMA_FATURA_CURSOR
FETCH NEXT FROM SATINALMA_FATURA_CURSOR INTO @FATURA
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @FLISTE = @FLISTE + @FATURA + ', '
	FETCH NEXT FROM SATINALMA_FATURA_CURSOR INTO @FATURA
END
	IF CHARINDEX(',', ISNULL(@FLISTE,' ')) <> 0
		SET @FLISTE = LEFT(@FLISTE, LEN(@FLISTE)-1)
return @FLISTE
END

GO


