SQL通用分页存储过程
SQL2000
/********************************************************************************** Function: SqlPage* Description:* 超强通用分页存储过程* Author: sharing* Finish DateTime:2005年9月24日*Example:* SuperPaging @TableName='表名',@Orderfld='排序列名'*********************************************************************************/CREATE PROCEDURE SqlPage(@TableNamenvarchar(50),-- 表名@RetuFieldsnvarchar(2000) = '*',-- 需要返回的列 @PageSizeint = 10,-- 每页记录数@PageIndexint = 1,-- 当前页码@Wherenvarchar(2000) = '',-- 查询条件@Orderfldnvarchar(2000),-- 排序字段名 最好为唯一主键@OrderTypeint = 1-- 排序类型 1:降序 其它为升序)ASDECLARE @TotalRecord intDECLARE @TotalPage intDECLARE @CurrentPageSize intDECLARE @TotalRecordForPageIndex intDECLARE @OrderBy nvarchar(255)DECLARE @CutOrderBy nvarchar(255)if @OrderType = 1BEGINset @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc 'set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc 'ENDelseBEGINset @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' asc,') + ' asc 'set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc 'END-- 记录总数declare @countSql nvarchar(4000) set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Whereexecute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord outSET @TotalPage=(@TotalRecord-1)/@PageSize+1SET @CurrentPageSize=@PageSizeIF(@TotalPage=@PageIndex)BEGINSET @CurrentPageSize=@TotalRecord%@PageSizeIF(@CurrentPageSize=0)SET @CurrentPageSize=@PageSizeEND-- 返回记录set @TotalRecordForPageIndex=@PageIndex*@PageSizeexec('SELECT * FROM(SELECT TOP '+@CurrentPageSize+' * FROM(SELECT TOP '+@TotalRecordForPageIndex+' '+@RetuFields+'FROM '+@TableName+' '+@Where+' '+@OrderBy+') TB2'+@CutOrderBy+') TB3 '+@OrderBy)-- 返回总页数和总记录数SELECT @TotalPage as PageCount,@TotalRecord as RecordCountGO
SQL2005
/********************************************************************************** Copyright (C) 2006 All Rights Reserved ** Function: SqlPage ** Description: ** Sql2005分页存储过程 ** Author:** sharing ** Finish DateTime: ** 2006/7/15 **Example: ** SqlPage @Tablename = 'Table1', @Retufields = '*',** @PageSize = 2, @PageIndex = 1, @Where = '', @Orderfld = 'id', ** @Ordertype = 0 **********************************************************************************/CREATE PROCEDURE dbo.SqlPage@TableNamenvarchar(200),-- 表名@RetuFieldsnvarchar(200) = '*',-- 需要返回的列 @PageSizeint = 10,-- 每页记录数@PageIndexint = 1,-- 当前页码@Wherenvarchar(200) = '',-- 查询条件@Orderfldnvarchar(200),-- 排序字段名 最好为唯一主键@OrderTypeint = 1-- 排序类型 1:降序 其它为升序ASDECLARE @TotalRecord intDECLARE @TotalPage intDECLARE @CurrentPageSize intDECLARE @TotalRecordForPageIndex intDECLARE @OrderBy nvarchar(255)if @OrderType = 1BEGINset @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc 'ENDelseBEGINset @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' asc,') + ' asc 'END-- 记录总数declare @countSql nvarchar(4000) set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Whereexecute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord outSET @TotalPage=(@TotalRecord-1)/@PageSize+1SET @CurrentPageSize=(@PageIndex-1)*@PageSize-- 返回记录set @TotalRecordForPageIndex=@PageIndex*@PageSizeexec('SELECT *FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@RetuFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS Supesoft_RowNoFROM '+@TableName+ ' ' + @Where +' ) AS TempSqlPageTableWHERE TempSqlPageTable.Supesoft_RowNo > '+@CurrentPageSize)-- 返回总页数和总记录数SELECT @TotalPage as PageCount,@TotalRecord as RecordCount
作者：波霸38
来源链接：https://www.cnblogs.com/sharing1986687846/p/10318058.html
版权声明：
1、JavaClub（https://www.javaclub.cn）以学习交流为目的，由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容，包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网，本站不享有版权，版权归原作者所有。
2、本站提供的内容仅用于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律的规定，不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容，请及时通知本站（javaclubcn@163.com），我们将第一时间核实后及时予以删除。
