频道导航

sql2005 存储过程分页代码

2019-01-03 MsSQL 前端之家

前端之家收集整理的这篇文章主要介绍了sql2005 存储过程分页代码，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

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

<div class="codebody" id="code6492"> @H_3013@create database Test @H301_3@on primary ( name='TestData.mdf',@H301_3@filename='D:\我的资料\sql\备份\TestData.mdf' @H3013@) @H3013@log on @H3013@( @H301_3@name='TestData.ldf',@H301_3@filename='D:\我的资料\sql\备份\TestData.ldf' @H301_3@) if objectid('tb') is not null drop table tb @H3013@create table tb @H3013@( @H3013@Col int @H3013@) @H301_3@insert into tb select top 50 number from master..sptvalues where type='P' and number>0 create proc SplitPage @H3013@( @H3013@@TableName nvarchar(50),@H301_3@@PageSize int,--每页显示的数量 @H_3013@@CurrentPage int,--当前第几页 @H3013@@PageCol nvarchar(50),--排序字段 @H3013@@OrderNo nvarchar(50)--排序方式(DESC,ASC) @H3013@) @H3013@as @H3013@/* @H3013@测试用的 @H3013@declare @PageCol nvarchar(50) @H3013@declare @TableName nvarchar(50) @H3013@declare @OrderNo nvarchar(50) @H3013@declare @PageSize int @H3013@declare @CurrentPage int @H3013@set @PageCol='Col' @H3013@set @TableName='tb' @H3013@set @OrderNo='DESC' @H3013@set @PageSize=10 @H3013@set @CurrentPage=4 @H3013@*/ @H301_3@declare @sql nvarchar(1000) @H_301_3@set @sql='' @H_301_3@set @sql=' @H_3013@;with hgo as @H3013@( @H301_3@select ,rownumber() over( @H3013@order by '+@PageCol+' '+@OrderNo+') rank @H3013@from '+@TableName+' @H3013@)' @H301_3@set @sql=@sql+'select Col from hgo where rank between '+ltrim((@CurrentPage-1)@PageSize+1)+' and '+ltrim(@CurrentPage*@PageSize) @H_301_3@--print @sql @H_301_3@exec (@sql) @H_3013@

@H3013@exec SplitPage 'tb',10,1,'Col','DESC' Col @H3013@----------- @H3013@50 @H3013@49 @H3013@48 @H3013@47 @H3013@46 @H3013@45 @H3013@44 @H3013@43 @H3013@42 @H3013@41 (10 行受影响) exec SplitPage 'tb',3,'DESC' @H3013@Col @H3013@----------- @H3013@30 @H3013@29 @H3013@28 @H3013@27 @H3013@26 @H3013@25 @H3013@24 @H3013@23 @H3013@22 @H301_3@21 (10 行受影响)

sql2005 分页分页分页存储过程