【www.bbyears.com--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 "+@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"