您的位置:365bet手机在线 > 365bet线上手机投注 > 通用性分页存储过程,通用分页存储过程实例

通用性分页存储过程,通用分页存储过程实例

发布时间:2020-03-19 13:27编辑:365bet线上手机投注浏览(131)

    一篇好用的通用分页存款和储蓄进度,能够用在别的付出上啊,只要小小的改培养好了,有亟待的朋友能够仿照效法一下本款实例。 代码如下复制代码 /*通用分页存款和储蓄进程*/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(1000卡塔尔 = '*', --须要获得的字段 @tableName varchar(100卡塔尔(قطر‎, --须要查询的表 @condition varchar(1000卡塔尔 = '', --查询条件, 不用加where关键字@ascColumn varchar(100卡塔尔 = '', --排序的字段名 (即 order by column asc/descState of Qatar@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 desc卡塔尔(قطر‎BEGINIF @bitOrderType = 1 --降序BEGINSET @strOrderType = ' O奥迪Q7DE大切诺基 BY '+@ascColumn+' DESC'SET @strTemp = '(SELECT min'ENDELSE--升序BEGINSET @strOrderType = ' OLANDDE奥迪Q5 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 TabTempState of Qatar'+@strOrderTypeENDENDEXEC (@strSql卡塔尔(قطر‎END--存款和储蓄进程甘休--分页得到客房音信列表测量检验EXEC cndoup_GetPageOfRecords 20,2,'房间号=RoomNum,房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID卡塔尔(قطر‎,房间状态=(SELECT HighlanderSDec 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.RoomTypeIDState of Qatar,房间状态=(SELECT 昂CoraSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusIDState of Qatar,BedNum,Floors,RoomDes,RoomRemark','Room','RoomNum=304','RoomID',0,'RoomID'

    /*通用分页存款和储蓄进度*/
    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/descState of Qatar
    @bitOrderType bit = 0,                    --排序的项目 (0为升序,1为降序卡塔尔
    @pkColumn varchar(50卡塔尔 = ''                --主键名称

    AS
    BEGIN                                          --存款和储蓄进程早前
    DECLARE @strTemp varchar(300)
    DECLARE @strSql varchar(5000卡塔尔              --该存款和储蓄进程最后试行的口舌
    DECLARE @strOrderType varchar(1000State of Qatar        --排序类型语句 (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+@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

    本文由365bet手机在线发布于365bet线上手机投注,转载请注明出处:通用性分页存储过程,通用分页存储过程实例

    关键词:

上一篇:分组统计语句实例代码

下一篇:没有了