Ok I solved it
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
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 *,(Select Max(RowNumberUrunAd) FROM Ara) as
SonucSayisi 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 *,(Select Max(RowNumberUrunFiyat) FROM Ara) as
SonucSayisi 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 * ,(Select Max(RowNumberUrunID) FROM Ara) as
SonucSayisi 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
