SQL Server 分页查询存储过程代码

复制代码代码如下:
CREATE PROCEDURE [dbo].[up_Pager]
@table varchar（2000）, --表名
@col varchar（50）, --按该列来进行分页
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar（800）,--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar（800） --查询条件
AS
DECLARE @sql nvarchar（4000）,@where1 varchar（800）,@where2 varchar（800）,
@total_Item int,@total_Page int
IF @condition is null or rtrim（@condition）=""
BEGIN--没有查询条件
SET @where1=" WHERE "
SET @where2=" "
END
ELSE
BEGIN--有查询条件
SET @where1=" WHERE （"+@condition+"） AND "--本来有条件再加上此条件
SET @where2=" WHERE （"+@condition+"） "--原本没有条件而加上此条件
END
SET @sql="SELECT @total_Item=CEILING（（COUNT（*）+0.0）"+"） FROM "+@table+ @where2
EXEC sp_executesql @sql,N"@total_Item int OUTPUT",@total_Item OUTPUT --计算总条数
set @total_Page = Ceiling（（@total_Item+0.0）/@pagesize） --计算页总数

IF @orderby=0
SET @sql="SELECT TOP "+CAST（@pagesize AS varchar）+" "+@collist+
" , "+ CAST（@total_Item AS varchar） + " as total_Item" +
" , "+CAST（@total_Page AS varchar） + " as total_Page" +
" FROM mailto:"+@table+@where1+@col+"%3E（SELECT MAX（"+@col+"） "+
" FROM （SELECT TOP "+CAST（@pagesize*（@page-1） AS varchar）+" "+
@col+" FROM "+@table+@where2+"ORDER BY "+@col+"） t） ORDER BY "+@col
ELSE
SET @sql="SELECT TOP "+CAST（@pagesize AS varchar）+" "+@collist+
" , "+ CAST（@total_Item AS varchar） + " as total_Item" +
" , "+CAST（@total_Page AS varchar） + " as total_Page" +
" FROM mailto:"+@table+@where1+@col+"%3C（select MIN（"+@col+"） "+
" FROM （SELECT TOP "+CAST（@pagesize*（@page-1） AS varchar）+" "+
@col+" FROM "+@table+@where2+"ORDER BY "+@col+" DESC） t） ORDER BY "+
@col+" DESC"
IF @page=1--第一页
SET @sql="SELECT TOP "+CAST（@pagesize AS varchar）+" "+@collist+
" , "+ CAST（@total_Item AS varchar） + " as total_Item" +
" , "+CAST（@total_Page AS varchar） + " as total_Page" +
" FROM "+@table+
@where2+"ORDER BY "+@col+CASE @orderby WHEN 0 THEN "" ELSE " DESC" END
--print @sql
EXEC（@sql）

在SQL中测试（教你如何使用）
复制代码代码如下:
EXEC up_Pager "（SELECT * FROM 表名）aa","要排序的列名",0-顺序或1-倒序,"显示列",每页记录数,指定页,"条件"
EXEC up_Pager "（SELECT * FROM T_Gather_Page）aa","SaveTime",1,"*",40,3,""