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,”

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

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)