SQL Server利用RowNumber()内置函数与Over关键字贯彻通用分页存款和储蓄进程(扶助单表或多表结查集分页)

SQL
Server利用RowNumber()内置函数与Over关键字贯彻通用分页存储进程,帮衬单表或多表结查集分页,存款和储蓄进程如下:

/******************/
--Author:梦在旅途(www.Zuowenjun.cn)
--CreateDate:2015-06-02
--Function:分页获取数据
/******************/
create procedure [dbo].[sp_DataPaging]
(
@selectsql  nvarchar(200),--查询字段SQL,不含select,支持灵活写法,如:col1,col3,isnull(col4,'') as col4
@fromsql    nvarchar(500),--查询表及条件SQL,不含from,若包含条件请加上where,如:table where col1='test'
@orderbysql nvarchar(100),--查询排序SQL,不含order by,如:id order by desc
@pagesize   int=20,--每页显示记录数
@pageno     int=1,--当前查询页码,从1开始
@returnrecordcount bit=1 --是否需要返回总记录数,若设为1,则返回两个结果表
)
as
begin

    declare @sqlcount nvarchar(500),@sqlstring nvarchar(max)

    set @sqlstring=N'from (select row_number() over (order by ' + @orderbysql + N') as rowId,' + @selectsql + N' from '
                + @fromsql +N') as t'
    if(@returnrecordcount=1)
    begin
        set @sqlcount=N'select count(rowId) as resultcount ' + @sqlstring
        exec(@sqlcount)
    end

    set @sqlstring=N'select * ' + @sqlstring + ' where rowId between ' + convert(nvarchar(50),(@pageno-1)*@pagesize+1)+' and '+convert(nvarchar(50),@pageno*@pagesize)

    exec(@sqlstring)

end

 用法如下:

--单表查询
exec [dbo].[sp_DataPaging] '*','AssetDetail','assetsingleno',10,1

--多表查询
exec [dbo].[sp_DataPaging] 'a.*','Inventory a left join AssetDetail b 
on a.StoreNo=b.StoreNo and a.CompanyID=b.CompanyID
inner join Asset c on b.AssetID=c.ID and b.CompanyID=c.CompanyID','a.id',20,3,1

结果呈现如下(如上多表查询):

图片 1

相关文章