欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQLSERVER Pager store procedure分页存储过程

程序员文章站 2023-12-16 20:28:40
复制代码 代码如下:set ansi_nulls on go set quoted_identifier on go create procedure [dbo].[pag...
复制代码 代码如下:

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#)

上一篇:

下一篇: