通用分页存储过程

/*通用分页存储过程*/
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 ' +@ascColumn+ ' DESC'
         SET @strTemp = '<(SELECT min'
       END
     ELSE                    --升序
       BEGIN
         SET @strOrderType = ' ORDER BY ' +@ascColumn+ ' ASC'
         SET @strTemp = '>(SELECT max'
       END

     IF @currentPage = 1             --第一页
       BEGIN
         IF @condition != ''
           SET @strSql = 'SELECT TOP ' + STR (@pageSize)+ ' ' +@columns+ ' FROM ' +@tableName+
             ' WHERE ' +@condition+@strOrderType
         ELSE
           SET @strSql = 'SELECT TOP ' + STR (@pageSize)+ ' ' +@columns+ ' FROM ' +@tableName+@strOrderType
       END

     ELSE                    -- 其他页
       BEGIN
         IF @condition != ''
           SET @strSql = 'SELECT TOP ' + STR (@pageSize)+ ' ' +@columns+ ' FROM ' +@tableName+
           ' WHERE ' +@condition+ ' AND ' +@pkColumn+@strTemp+ '(' +@pkColumn+ ')' + ' FROM (SELECT TOP ' + STR ((@currentPage-1)*@pageSize)+
           ' ' +@pkColumn+ ' FROM ' +@tableName 'where' +@condition+@strOrderType+ ') AS TabTemp)' +@strOrderType
         ELSE
           SET @strSql = 'SELECT TOP ' + STR (@pageSize)+ ' ' +@columns+ ' FROM ' +@tableName+
           ' WHERE ' +@pkColumn+@strTemp+ '(' +@pkColumn+ ')' + ' FROM (SELECT TOP ' + STR ((@currentPage-1)*@pageSize)+ ' ' +@pkColumn+
           ' FROM ' +@tableName+@strOrderType+ ') 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'


--·  得到客房类型信息列表测试
EXEC cndoup_GetPageOfRecords 10,1, 'RoomTypeDes,Price,Area,AddBed,MaxBedNum,BedPrice,HourRoom,HourPrice,Remark' , 'RoomType' , '' , 'RoomTypeID' ,0, 'RoomTypeID'