当前位置: 代码迷 >> Sql Server >> 轨范通用分页存储过程,通用性极强
  详细解决方案

轨范通用分页存储过程,通用性极强

热度:103   发布时间:2016-04-27 11:24:44.0
规范通用分页存储过程,通用性极强!
昨天写存储过程时遇到一个麻烦,再搜寻答案时搜到了“通用存储过程”,但只看到部分残缺代码,多方参考后,鄙人对其进行总结规范,得出以下规范存储过程,竟然把本人项目里50多个查询存储过程代替!不敢独享,遂贴出与大家分享,希望对大家有所帮助!!

该存储过程几乎可以涵盖所有查询存储过程(目前本人还没有发现不能使用的)

参数:分页大小,第几页,需要得到的字段 ,需要查询的表 , 查询条件,排序的字段名,排序的类型,主键名称

输入参数即可查询,无论是查询一条记录还是查询多条记录,无论是分页还是不分页,无论是需要查询条件还是不需要查询条件……,都可使用此通用的存储过程!!!
通用性相当好!!



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

IF @currentPage = 1 --第一页
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' [email protected]+' FROM [email protected]+
' WHERE [email protected][email protected]
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' [email protected]+' FROM [email protected][email protected]
END

ELSE -- 其他页
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' [email protected]+' FROM [email protected]+
' WHERE [email protected]+' AND [email protected][email protected]+'([email protected]+')'+' FROM (SELECT TOP '+STR((@currentPage-1)[email protected])+
' [email protected]+' FROM [email protected][email protected]+') AS TabTemp)[email protected]
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' [email protected]+' FROM [email protected]+
' WHERE [email protected][email protected]+'([email protected]+')'+' FROM (SELECT TOP '+STR((@currentPage-1)[email protected])+' [email protected]+
' FROM [email protected][email protected]+') AS TabTemp)[email protected]
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'



------解决方案--------------------
回头看了下,发现一处错误:
/*通用分页存储过程*/ 
USE HotelManagementSystem 
GO 
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords') 
  相关解决方案