更新时间:2022-09-14 12:19:33
SQL Server利用RowNumber()内置函数与Over关键字实现通用分页存储过程,支持单表或多表结查集分页,存储过程如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
/******************/ --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 |
用法如下:
1
2
3
4
5
6
7
|
--单表查询 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
|
结果显示如下(如上多表查询):
本文转自 梦在旅途 博客园博客,原文链接:http://www.cnblogs.com/zuowj/p/4546079.html ,如需转载请自行联系原作者