存储过程分页

create database Test
on primary ( name='Test_Data.mdf',
  filename
='D:/我的资料/sql/备份/Test_Data.mdf'
)
log on
(
  name
='Test_Data.ldf',
  filename
='D:/我的资料/sql/备份/Test_Data.ldf'
)

if object_id('tb') is not null drop table tb
create table tb
(
  Col
int
)
insert into tb select top 50 number from master..spt_values where type='P' and number>0

create proc SplitPage
(
 
@TableName nvarchar(50),
 
@PageSize int,--每页显示的数量
  @CurrentPage int,--当前第几页
  @PageCol nvarchar(50),--排序字段
  @OrderNo nvarchar(50)--排序方式(DESC,ASC)
)
as
/*
测试用的
declare @PageCol nvarchar(50)
declare @TableName nvarchar(50)
declare @OrderNo nvarchar(50)
declare @PageSize int
declare @CurrentPage int
set @PageCol='Col'
set @TableName='tb'
set @OrderNo='DESC'
set @PageSize=10
set @CurrentPage=4
*/
declare @sql nvarchar(1000)
set @sql=''
set @sql='
;with hgo as
(
  select *,row_number() over(
  order by
'+@PageCol+' '+@OrderNo+') rank
  from
'+@TableName+'
)
'
set @sql=@sql+'select Col from hgo where rank between '+ltrim((@CurrentPage-1)*@PageSize+1)+' and '+ltrim(@CurrentPage*@PageSize)
--print @sql
exec (@sql)

exec SplitPage 'tb',10,1,'Col','DESC'

Col
-----------
50
49
48
47
46
45
44
43
42
41

(
10 行受影响)

exec SplitPage 'tb',10,3,'Col','DESC'
Col
-----------
30
29
28
27
26
25
24
23
22
21

(
10 行受影响)

阅读更多
个人分类: MSSQL
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭