sql2005 存储过程分页代码示例

发布时间：2023-08-07 14:23:02 所属栏目：MsSql教程来源：

导读：<div class="codetitle"><a style="CURSOR: pointer" data="6492" class="copybut" id="copybut6492" onclick="doCopy('code6492')"> 代码如下:

<div class="codebody&quo

<div class="codetitle"><a style="CURSOR: pointer" data="6492" class="copybut" id="copybut6492" onclick="doCopy('code6492')"> 代码如下:

<div class="codebody" id="code6492">

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',3,'DESC'

Col

-----------

30

29

28

27

26

25

24

23

22

21 (10 行受影响)

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!