I dynamically create my query based on filter criteria , order by criteria
and paging criterias. I have no promlem at that point.

My problem is that How can i get total record number of my recordset in
stored procedure. What is the best way to get it. Should i use and output
parameter in stored procedure and

return it to outside? can i have a chance to return a executed selecy query
and a output paramater .

Or other possiblity how can i put it into my query to return it with other
records ?

Any ideas ?

Thanks for your helping.

ALTER PROCEDURE [dbo].[ST_ARAMA]

-- Add the parameters for the stored procedure here

@Aranacak_Metin VARCHAR(255),

@Kacinci_Sayfa INT,

@Sayfa_KayitSayisi INT ,

@Siralama_Kosul VARCHAR(10) ,

@Siralama_OrderBy VARCHAR(5),

AS

BEGIN

SET NOCOUNT ON;

Declare @IlkKayit INT

Declare @SonKayit INT

Declare @Sorgu VARCHAR(2000)

If @Kacinci_Sayfa > 0

BEGIN

SET @Kacinci_Sayfa = @Kacinci_Sayfa -1

SET @IlkKayit = @Sayfa_KayitSayisi * @Kacinci_Sayfa + 1;

SET @SonKAyit = @IlkKayit + @IlkKayit - 1 ;

SEt @Sorgu ='

With Ara AS (

SELECT TBL_MARKA.Marka_Ad + '' '' + Urun_Ad as guUrun_Ad

,dbo.FN_KURHESAPLA(Urun_Parabirim_ID,Urun_Fiyat) AS guUrun_Fiyat

,TBL_MARKA.Marka_Ad + ''/'' + Urun_Ad + ''.jpg'' AS guUrun_Resim

,TBL_CINSIYET.Cinsiyet_Ad as guCinsiyet_Ad

, Urun_ID as guUrun_ID

,TBL_MODEL.Model_Ad as guModel_Ad

,ROW_NUMBER() OVER (order by TBL_URUNLER.Urun_ID) as RowNumberUrunID

,ROW_NUMBER() OVER (order by TBL_URUNLER.Urun_Fiyat) as RowNumberUrunFiyat

,ROW_NUMBER() OVER (order by TBL_URUNLER.Urun_Ad) as RowNumberUrunAd

FROM TBL_MARKAMODELURUN

INNER JOIN TBL_URUNLER

ON TBL_URUNLER.Urun_ID = TBL_MARKAMODELURUN.MarkaModel_Urun_ID

INNER JOIN TBL_MARKA

ON TBL_MARKAMODELURUN.MarkaModel_Marka_ID = TBL_MARKA.Marka_ID

INNER JOIN TBL_MODEL

ON TBL_MARKAMODELURUN.MarkaModel_Model_ID = dbo.TBL_MODEL.Model_ID

INNER JOIN TBL_CINSIYET

ON TBL_URUNLER.Urun_Cinsiyet_ID = TBL_CINSIYET.Cinsiyet_ID

WHERE CONTAINS(Urun_Ad, N''FORMSOF (INFLECTIONAL,' + @Aranacak_Metin +' )'')

OR CONTAINS(Urun_Ozellik, N''FORMSOF (INFLECTIONAL,'+@Aranacak_Metin+')'')

OR CONTAINS(Marka_Ad, N''FORMSOF (INFLECTIONAL,'+@Aranacak_Metin+')'')

OR CONTAINS(Model_Ad, N''FORMSOF (INFLECTIONAL,'+@Aranacak_Metin+')'')

OR CONTAINS(Cinsiyet_Ad, N''FORMSOF (INFLECTIONAL,'+@Aranacak_Metin+')'')

)

'

If @Siralama_Kosul ='ad'

Set @Sorgu = @Sorgu + 'SELECT * FROM Ara WHERE RowNumberUrunAd > ' +
Convert(VARCHAR(5),@IlkKayit)+' and RowNumberUrunAd < ' + CONVERT
(VARCHAR(5),@SonKayit) +' ORDER BY RowNumberUrunAd '

If @Siralama_Kosul ='fiyat'

Set @Sorgu = @Sorgu + 'SELECT * FROM Ara WHERE RowNumberUrunFiyat > ' +
Convert(VARCHAR(5),@IlkKayit)+' and RowNumberUrunFiyat < ' + CONVERT
(VARCHAR(5),@SonKayit) +' ORDER BY RowNumberUrunFiyat'

If @Siralama_Kosul ='sira'

Set @Sorgu = @Sorgu + 'SELECT * FROM Ara WHERE RowNumberUrunID > ' +
Convert(VARCHAR(5),@IlkKayit)+' and RowNumberUrunID < ' + CONVERT
(VARCHAR(5),@SonKayit) +' ORDER BY RowNumberUrunID'

If @Siralama_OrderBy='asc'

Set @Sorgu = @Sorgu + ' ASC'

If @Siralama_OrderBy='desc'

Set @Sorgu = @Sorgu + ' DESC '

--Print @Sorgu

Exec (@Sorgu)

END

END