mssql 通用分页存储过程实例

一篇好用的通用分页存储过程，可以用在任何开发上哦，只要小小的改动就好了，有需要的朋友可以参考一下本款实例。

代码如下

复制代码

/*通用存储过程*/ USE HotelManagementSystem GO IF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords') DROP PROCEDURE cndoup_GetPageOfRecords GO --创建存储过程 CREATE PROCEDURE cndoup_GetPageOfRecords @pageSize int = 20,                        --分页大小 @currentPage int ,                         --第几页 @columns varchar(1000) = '*',              --需要得到的字段 @tableName varchar(100),                   --需要查询的表  @condition varchar(1000) = '',             --查询条件, 不用加where关键字 @ascColumn varchar(100) = '',              --排序的字段名 (即 order by column asc/desc) @bitOrderType bit = 0,                     --排序的类型 (0为升序,1为降序) @pkColumn varchar(50) = ''                 --主键名称   AS BEGIN                                          --存储过程开始 DECLARE @strTemp varchar(300) DECLARE @strSql varchar(5000)              --该存储过程最后执行的语句 DECLARE @strOrderType varchar(1000)        --排序类型语句 (order by column asc或者order by column desc)   BEGIN IF @bitOrderType = 1   --降序 BEGIN SET @strOrderType = ' ORDER BY DESC' SET @strTemp = ' END ELSE--升序 BEGIN SET @strOrderType = ' ORDER BY ASC' SET @strTemp = '>(SELECT max' END   IF @currentPage = 1--第一页 BEGIN IF @condition != '' SET @strSql = 'SELECT TOP '+STR(@pageSize)+' FROM + ' WHERE ELSE SET @strSql = 'SELECT TOP '+STR(@pageSize)+' FROM END   ELSE-- 其他页 BEGIN IF @condition !='' SET @strSql = 'SELECT TOP '+STR(@pageSize)+' FROM + ' WHERE AND FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+ ' FROM ) AS TabTemp)'+@strOrderType ELSE SET @strSql = 'SELECT TOP '+STR(@pageSize)+' FROM + ' WHERE FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' + ' FROM ) AS TabTemp)'+@strOrderType END   END EXEC (@strSql) END   --存储过程结束     --分页得到客房信息列表测试 EXEC cndoup_GetPageOfRecords 20,2,'房间号=RoomNum, 房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID), 房间状态=(SELECT RSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID), 床位数=BedNum, 楼层=Floors, 描述=RoomDes, 备注=RoomRemark','Room','','RoomID',0,'RoomID'     --根据房间号得到客房信息测试 EXEC cndoup_GetPageOfRecords 1,1,'房间号=RoomNum, 房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID), 房间状态=(SELECT RSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID), BedNum, Floors, RoomDes, RoomRemark','Room','RoomNum=304','RoomID',0,'RoomID'