通用分页存储过程 GoBlog
转自:http://51cndo.blog.51cto.com/491222/102320
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'