复制代码 代码如下:
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[pagination]
@page int = 1, — 当前页码
@pagesize int = 10, — 每页记录条数(页面大小)
@table nvarchar(500), — 表名或视图名，甚至可以是嵌套sql：(select * from tab where id>1000) tab
@field nvarchar(800) = ‘*’, — 返回记录集字段名，”,”隔开，默认是”*”
@orderby nvarchar(100) = ‘id asc’, — 排序规则
@filter nvarchar(500), — 过滤条件
@maxpage smallint output, — 执行结果 -1 error, 0 false, maxpage true
@totalrow int output, — 记录总数 /* 2007-07-12 22:11:00 update */
@descript varchar(100) output — 结果描述
as
begin
— =============================================
— author: jimmy.yu
— create date: 2007-5-11
— description: sql 2005 以上版本 通用分页存储过程
— =============================================
set rowcount @pagesize;
set @descript = ‘successful’;
——————-参数检测—————-
if len(rtrim(ltrim(@table))) !> 0
begin
set @maxpage = 0;
set @descript = ‘table name is empty’;
return;
end
if len(rtrim(ltrim(@orderby))) !> 0
begin
set @maxpage = 0;
set @descript = ‘order is empty’;
return;
end
if isnull(@pagesize,0) <= 0
begin
set @maxpage = 0;
set @descript = ‘page size error’;
return;
end
if isnull(@page,0) <= 0
begin
set @maxpage = 0;
set @descript = ‘page error’;
return;
end
——————-检测结束—————-
begin try
— 整合sql
declare @sql nvarchar(4000), @portion nvarchar(4000);
set @portion = ‘ row_number() over (order by ‘ + @orderby + ‘) as rownum from ‘ + @table;
set @portion = @portion + (case when len(@filter) >= 1 then (‘ where ‘ + @filter + ‘) as tab’) else (‘) as tab’) end);
set @sql = ‘select top(‘ + cast(@pagesize as nvarchar(8)) + ‘) ‘ + @field + ‘ from (select ‘ + @field + ‘,’ + @portion;
set @sql = @sql + ‘ where tab.rownum > ‘ + cast((@page-1)*@pagesize as nvarchar(8));
— 执行sql, 取当前页记录集
execute(@sql);
——————————————————————–
— 整合sql
set @sql = ‘set @rows = (select max(rownum) from (select’ + @portion + ‘)’;
— 执行sql, 取最大页码
execute sp_executesql @sql, n’@rows int output’, @totalrow output;
set @maxpage = (case when (@totalrow % @pagesize)<>0 then (@totalrow / @pagesize + 1) else (@totalrow / @pagesize) end);
end try
begin catch
— 捕捉错误
set @maxpage = -1;
set @descript = ‘error line: ‘ + cast(error_line() as varchar(8)) + ‘, error number: ‘ + cast(error_number() as varchar(8)) + ‘, error message: ‘ + error_message();
return;
end catch;
— 执行成功
return;
end
相对应的页面逻辑中写的对应调用该存储过程的方法（c#)