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)

EXEC up_Pager (SELECT * FROM 表名)aa,要排序的列名,0-顺序或1-倒序,显示列,每页记录数,指定页,条件
EXEC up_Pager (SELECT * FROM T_Gather_Page)aa,SaveTime,1,*,40,3,