paging with sql

Question

Post reply

paging with sql

cotzan

SSC Enthusiast

Points: 160
More actions
August 17, 2009 at 1:25 am

#132776

Hi Masters.
i have a paging Sp named SpPaging. when i exec it like below
EXEC SpPaging
'Select DISTINCT InEmailID,StEmailBaslik,StKullaniciAdi AS StKullaniciAdi,DtEmailTarih from EMAILLER ',
' ',
' order by InEmailID',
1,
13,''
its work. but when i exec with dots like below its not work.
EXEC SpPaging
'Select DISTINCT e.InEmailID,e.StEmailBaslik,e.StKullaniciAdi AS StKullaniciAdi,e.DtEmailTarih from EMAILLER as e',
' ',
' order by InEmailID',
1,
13,''
THE SP IS THAT:
ALTER PROCEDURE [dbo].[SpPaging]
@prmStQuerynvarchar(MAX),
@prmWherenvarchar(MAX),--ölçüt
@prmStOrderBynvarchar(100),--sıralama
@prmStartIndexint,--Başlangıç satır no
@prmRowCountint,--bir sayfayda gösterilecek satır sayısı
@prmStCountQuerynvarchar(MAX)--Count sorgusu(opsiyonel)
AS
DECLARE @varCountQuery varchar(MAX)
SET @varCountQuery=@prmStQuery
DECLARE @varTmp varchar(4000)
SET @varTmp='(' +
REPLACE(@prmStQuery,'from',
(',ROW_NUMBER() OVER(' + @prmStOrderBy + ') AS ROWNUM FROM'))
SET @varTmp=@varTmp + ' ' + @prmWhere + ') as c'
DECLARE @varLen int
SET @varLen=CHARINDEX('from',@prmStQuery)+3
SET @prmStQuery=SUBSTRING(@prmStQuery,1,@varLen)
SET @prmStQuery=@prmStQuery + @varTmp
IF @prmWhere IS NOT NULL AND LEN(@prmWhere)>0
SET @prmStQuery = @prmStQuery + ' ' + ' WHERE (ROWNUM>=' + CAST(@prmStartIndex as varchar(10)) + ' AND ROWNUM<' + CAST((@prmStartIndex + @prmRowCount) as varchar(10)) + ')'
ELSE
SET @prmStQuery = @prmStQuery + ' Where (ROWNUM>=' + CAST(@prmStartIndex as varchar(10)) + ' AND ROWNUM<' + CAST((@prmStartIndex + @prmRowCount) as varchar(10)) + ')'
IF @prmStCountQuery IS NULL OR LEN(@prmStCountQuery)=0
BEGIN
SET @varCountQuery = RIGHT(@varCountQuery,(LEN(@varCountQuery)+1 - PATINDEX('%from%',@varCountQuery)))
SET @varCountQuery = 'SELECT COUNT(*) ' + @varCountQuery + ' ' + @prmWhere
END
ELSE
SET @varCountQuery=@prmStCountQuery
EXEC(@prmStQuery)--recordset
EXEC(@varCountQuery)--maxcount
REGARDS

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

How exactly does it not work ?

Does it error or return no rows ?

Clear Sky SQL
My Blog[/url]

cotzan SSC Enthusiast Points: 160 More actions · Answer 2

i solve it. thank you so much. and more simple question, i push 'qouted' button for you. is it give mark for you??

cotzan SSC Enthusiast Points: 160 More actions · Answer 3

Dave Ballantyne (8/17/2009)
How exactly does it not work ?
Does it error or return no rows ?

i solve it. thank you so much. and more simple question, i push 'qouted' button for you. is it give mark for you??