＋设为首页 ＋添加到收藏夹
在一个分页存储过程中看到：
附代码：
create PROCEDURE dbo.GetPagingData
(
@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 @sqlstr=declare @Rcount int;
set @sqlstr=@sqlstr+set @rcount=(select count(+@keyfield+) from +@tablename+ where +@strWhere+);
set @strwhere=replace(@strwhere,,)
set @strwhere=replace(@strwhere,--,)
set @strwhere=replace(@strwhere,;,)
set @sqlstr=@sqlstr+declare @Rnum int;
set @sqlstr=@sqlstr+set @rnum=@rcount-+cast(@pagesize as varchar)+*+cast(@pageindex as varchar)+;
set @sqlstr=@sqlstr+declare @sqlstr varchar(6000);
if @ordertype=1
begin
set @sqlstr=@sqlstr+set @sqlstr=select top +cast(@Pagesize as varchar)+ +@fieldlist+ from (select top 100
percent * from (select top +cast(@rnum as varchar)+ * from +@tablename+ where +@strwhere+
order by +@orderfield+ asc) as b order by paymoney desc) as a order by +@orderfield+ desc ;
end
else
begin
set @sqlstr=@sqlstr+set @sqlstr=select top +cast(@Pagesize as varchar)+ +@fieldlist+ from (select top 100
percent * from (select top +cast(@rnum as varchar)+ * from +@tablename+ where +@strwhere+
order by +@orderfield+ desc) as b order by paymoney asc) as a order by +@orderfield+ asc ;
end
set @sqlstr=@sqlstr+if @Rcount>0 begin execute(@sqlstr) end
--print @sqlstr
execute(@sqlstr)
paymoney --应该是一个字段的名字
@tablename 的谋字段
