create
PROCEDURE
dbo.GetPagingRecord
(
@tablename
varchar
(
100
),
--
表名或视图表
@fieldlist
varchar
(
4000
)
=
'
*
'
,
--
欲选择字段列表
@orderfield
varchar
(
100
),
--
排序字段
@keyfield
varchar
(
100
),
--
主键
@pageindex
int
,
--
页号,从0开始
@pagesize
int
=
20
,
--
页尺寸
@strwhere
varchar
(
4000
),
--
条件
@ordertype
bit
=
1
--
排序,1,降序,0,升序
)
AS
/*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明 
*/
SET
NOCOUNT
ON
declare
@sqlstr
varchar
(
6000
)
--
处理SQL中危险字符,并且将条件处理成易嵌入的形式
set
@strwhere
=
replace
(@strwhere,
''''
,
''''''
)
set
@strwhere
=
replace
(@strwhere,
'
--
'
,
''
)
set
@strwhere
=
replace
(@strwhere,
'
;
'
,
''
)
set
@sqlstr
=
'
declare @CurPageNum int;
'
set
@sqlstr
=
@sqlstr
+
'
declare @nextpagenum int;
'
set
@sqlstr
=
@sqlstr
+
'
set @curpagenum=
'
+
cast
(@PageIndex
as
varchar
)
+
'
*
'
+
cast
(@Pagesize
as
varchar
)
+
'
;
'
set
@sqlstr
=
@sqlstr
+
'
set @nextpagenum=
'
+
cast
(@PageIndex
+
1
as
varchar
)
+
'
*
'
+
cast
(@Pagesize
as
varchar
)
+
'
;
'
set
@sqlstr
=
@sqlstr
+
'
declare @sqlstr varchar(6000);
'
if
@ordertype
=
1
begin
set
@sqlstr
=
@sqlstr
+
'
set @sqlstr=
''
select
'
+
@fieldlist
+
'
from ( select top
''
+cast(@nextpagenum as varchar)+
''
* from
'
+
@tablename
+
'
where
'
+
@strwhere
+
'
order by
'
+
@orderfield
+
'
desc ) as a where
'
+
@keyfield
+
'
not in (
select top
''
+cast(@curpagenum as varchar)+
''
'
+
@keyfield
+
'
from
'
+
@tablename
+
'
where
'
+
@strwhere
+
'
order by
'
+
@orderfield
+
'
desc) order by
'
+
@orderfield
+
'
desc
''
;
'
end
else
begin
set
@sqlstr
=
@sqlstr
+
'
set @sqlstr=
''
select
'
+
@fieldlist
+
'
from ( select top
''
+cast(@nextpagenum as varchar)+
''
* from
'
+
@tablename
+
'
where
'
+
@strwhere
+
'
order by
'
+
@orderfield
+
'
asc ) as a where
'
+
@keyfield
+
'
not in (
select top
''
+cast(@curpagenum as varchar)+
''
'
+
@keyfield
+
'
from
'
+
@tablename
+
'
where
'
+
@strwhere
+
'
order by
'
+
@orderfield
+
'
asc) order by
'
+
@orderfield
+
'
asc
''
;
'
end
set
@sqlstr
=
@sqlstr
+
'
execute( @sqlstr)
'
--
print @sqlstr
execute
(@sqlstr)
==========================================================
2009-9-1 gdjlc 修改：加上了返回记录总数
CREATE PROCEDURE dbo.GetPagingRecord
(
@tablename varchar(100),--表名或视图表
@fieldlist varchar(4000)='*',--欲选择字段列表
@orderfield varchar(100),--排序字段
@keyfield varchar(100),--主键
@pageindex int,--页号,从0开始
@pagesize int=20,--页尺寸
@strwhere varchar(4000),--条件
@ordertype bit=1--排序,1,降序,0,升序
)
AS
/**//*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
*/
SET NOCOUNT ON
declare @sqlstr varchar(6000)
--处理SQL中危险字符,并且将条件处理成易嵌入的形式
set @strwhere=replace(@strwhere,'''','''''')
set @strwhere=replace(@strwhere,'--','')
set @strwhere=replace(@strwhere,';','')
set @sqlstr='declare @CurPageNum int;'
set @sqlstr=@sqlstr+'declare @nextpagenum int;'
set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';'
set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';'
set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
if @ordertype=1
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+'
from ( select top ''+cast(@nextpagenum as varchar)+'' * from
'+@tablename+'
where '+@strwhere+'
order by '+@orderfield+'
desc ) as a where '+@keyfield+'
not in (
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+'
from '+@tablename+'
where '+@strwhere+'
order by '+@orderfield+'
desc) order by '+@orderfield+'
desc'';'
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+'
from ( select top ''+cast(@nextpagenum as varchar)+'' * from
'+@tablename+'
where '+@strwhere+'
order by '+@orderfield+'
asc ) as a where '+@keyfield+'
not in (
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+'
from '+@tablename+'
where '+@strwhere+'
order by '+@orderfield+'
asc) order by '+@orderfield+'
asc'';'
end
begin
set @sqlstr =@sqlstr
+ ' select count(*) as Total from ' + @tablename + ' where '+ @strwhere + ';'
end
set @sqlstr=@sqlstr+'execute( @sqlstr)'
--print @sqlstr
execute(@sqlstr)
GO
-----------------------------------------------------------------------
exec GetPagingRecord 'product','id,c_code,p_code,price','price','id',1,10,'1=1',1