【lom599乐百家手机】通用分页存款和储蓄进度实例,通用性分页存款和储蓄进度

 联系我们     |      2020-05-07 09:30

一篇好用的通用分页存储进程,能够用在其余付出上啊,只要小小的改培育好了,有亟待的心上人能够参照一下本款实例。 代码如下复制代码 /*通用分页存款和储蓄进程*/USE HotelManagementSystemGOIF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords')DROP PROCEDURE cndoup_GetPageOfRecordsGO--创立存款和储蓄进程CREATE PROCEDURE cndoup_GetPageOfRecords@pageSize int = 20, --分页大小@currentPage int , --第几页@columns varchar(1000State of Qatar = '*', --必要取得的字段 @tableName varchar(100卡塔尔, --必要查询的表 @condition varchar(1000卡塔尔(قطر‎ = '', --查询条件, 不用加where关键字@ascColumn varchar(100卡塔尔 = '', --排序的字段名 (即 order by column asc/desc卡塔尔国@bitOrderType bit = 0, --排序的项目 (0为升序,1为降序卡塔尔(قطر‎@pkColumn varchar(50卡塔尔(قطر‎ = '' --主键名称ASBEGIN --存款和储蓄进度初步DECLARE @strTemp varchar(300卡塔尔国DECLARE @strSql varchar(5000卡塔尔国--该存款和储蓄进程最后实施的语句DECLARE @strOrderType varchar(1000卡塔尔--排序类型语句 (order by column asc或许order by column descState of QatarBEGINIF @bitOrderType = 1 --降序BEGINSET @strOrderType = ' O奥迪Q5DE安德拉 BY '+@ascColumn+' DESC'SET @strTemp = '(SELECT min'ENDELSE--升序BEGINSET @strOrderType = ' OCR-VDETiggo BY '+@ascColumn+' ASC'SET @strTemp = '(SELECT max'ENDIF @currentPage = 1--第一页BEGINIF @condition != ''SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+' WHERE '+@condition+@strOrderTypeELSESET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderTypeENDELSE-- 其他页BEGINIF @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)'+@strOrderTypeELSESET @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卡塔尔(قطر‎'+@strOrderTypeENDENDEXEC (@strSql卡塔尔(قطر‎END--存款和储蓄进度甘休--分页得到客房音讯列表测量试验EXEC cndoup_GetPageOfRecords 20,2,'房间号=RoomNum,房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID卡塔尔国,房间状态=(SELECT 中华VSDec 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 TiguanSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID卡塔尔国,BedNum,Floors,RoomDes,RoomRemark','Room','RoomNum=304','RoomID',0,'RoomID'

END
EXEC (@strSql)
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
lom599乐百家手机 ,END

AS
BEGIN                                          --存款和储蓄进程起首
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000卡塔尔              --该存款和储蓄进程最后施行的语句
DECLARE @strOrderType varchar(1000卡塔尔        --排序类型语句 (order by column asc也许order by column desc卡塔尔

/*测试*/
--分页获得客房音讯列表测验
EXEC cndoup_GetPageOfRecords 20,2,'房间号=RoomNum,
屋家状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID卡塔尔国,
房间状态=(SELECT LacrosseSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID卡塔尔,
床位数=BedNum,
楼层=Floors,
描述=RoomDes,
备注=RoomRemark','Room','','RoomID',0,'RoomID'