|
Posted on
2013-10-30 18:35
米粒3
阅读( 198)
评论()
编辑
收藏
举报
-TOP n 实现的通用分页存储过程(邹建)
CREATE PROC sp_PageView @tbname sysname, --要分页显示的表名
@FieldKey nvarchar( 1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar( 1000) ='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar( 1000) ='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) 用于指定排序顺序 @Where nvarchar( 1000) ='', --查询条件
@PageCount int OUTPUT --总页数
AS SET NOCOUNT ON --检查对象是否有效
IF OBJECT_ID( @tbname) IS NULL BEGIN RAISERROR(N '对象"%s"不存在', 1, 16, @tbname) RETURN END IF OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsTable') =0 AND OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsView') =0 AND OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsTableFunction') =0 BEGIN RAISERROR(N '"%s"不是表、视图或者表值函数', 1, 16, @tbname) RETURN END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --分页字段检查
IF ISNULL( @FieldKey,N '') ='' BEGIN RAISERROR(N '分页处理需要主键(或者惟一键)', 1, 16) RETURN END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --其他参数检查及规范
IF ISNULL( @PageCurrent, 0) <1 SET @PageCurrent=1 IF ISNULL( @PageSize, 0) <1 SET @PageSize=10 IF ISNULL( @FieldShow,N '') =N '' SET @FieldShow=N '*' IF ISNULL( @FieldOrder,N '') =N '' SET @FieldOrder=N '' ELSE SET @FieldOrder=N 'ORDER BY '+LTRIM( @FieldOrder) IF ISNULL( @Where,N '') =N '' SET @Where=N '' ELSE SET @Where=N 'WHERE ('+@Where+N ')'![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL BEGIN DECLARE @sql nvarchar( 4000) SET @sql=N 'SELECT @PageCount=COUNT(*)' +N ' FROM '+@tbname +N ' '+@Where EXEC sp_executesql @sql,N '@PageCount int OUTPUT', @PageCount OUTPUT SET @PageCount=( @PageCount+@PageSize-1) /@PageSize END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --计算分页显示的TOPN值
DECLARE @TopN varchar( 20), @TopN1 varchar( 20) SELECT @TopN=@PageSize, @TopN1=( @PageCurrent-1) *@PageSize![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --第一页直接显示
IF @PageCurrent=1 EXEC(N 'SELECT TOP '+@TopN +N ' '+@FieldShow +N ' FROM '+@tbname +N ' '+@Where +N ' '+@FieldOrder) ELSE BEGIN --处理别名
IF @FieldShow=N '*' SET @FieldShow=N 'a.*'![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar( 4000), @Where2 nvarchar( 4000), @s nvarchar( 1000), @Field sysname SELECT @Where1=N '', @Where2=N '', @s=@FieldKey WHILE CHARINDEX(N ',', @s) >0 SELECT @Field=LEFT( @s, CHARINDEX(N ',', @s) -1), @s=STUFF( @s, 1, CHARINDEX(N ',', @s),N ''), @Where1=@Where1+N ' AND a.'+@Field+N '=b.'+@Field, @Where2=@Where2+N ' AND b.'+@Field+N ' IS NULL', @Where=REPLACE( @Where, @Field,N 'a.'+@Field), @FieldOrder=REPLACE( @FieldOrder, @Field,N 'a.'+@Field), @FieldShow=REPLACE( @FieldShow, @Field,N 'a.'+@Field) SELECT @Where=REPLACE( @Where, @s,N 'a.'+@s), @FieldOrder=REPLACE( @FieldOrder, @s,N 'a.'+@s), @FieldShow=REPLACE( @FieldShow, @s,N 'a.'+@s), @Where1=STUFF( @Where1+N ' AND a.'+@s+N '=b.'+@s, 1, 5,N ''), @Where2=CASE WHEN @Where='' THEN N 'WHERE (' ELSE @Where+N ' AND (' END+N 'b.'+@s+N ' IS NULL'+@Where2+N ')'![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --执行查询
EXEC(N 'SELECT TOP '+@TopN +N ' '+@FieldShow +N ' FROM '+@tbname +N ' a LEFT JOIN(SELECT TOP '+@TopN1 +N ' '+@FieldKey +N ' FROM '+@tbname +N ' a '+@Where +N ' '+@FieldOrder +N ')b ON '+@Where1 +N ' '+@Where2 +N ' '+@FieldOrder) END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--临时表缓存实现的通用分页存储过程(邹建)
CREATE PROC sp_PageView @tbname sysname, --要分页显示的表名
@FieldKey nvarchar( 1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar( 1000) ='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar( 1000) ='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
@Where nvarchar( 1000) ='', --查询条件
@PageCount int OUTPUT --总页数
AS SET NOCOUNT ON --检查对象是否有效
IF OBJECT_ID( @tbname) IS NULL BEGIN RAISERROR(N '对象"%s"不存在', 1, 16, @tbname) RETURN END IF OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsTable') =0 AND OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsView') =0 AND OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsTableFunction') =0 BEGIN RAISERROR(N '"%s"不是表、视图或者表值函数', 1, 16, @tbname) RETURN END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --分页字段检查
IF ISNULL( @FieldKey,N '') ='' BEGIN RAISERROR(N '分页处理需要主键(或者惟一键)', 1, 16) RETURN END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --其他参数检查及规范
IF ISNULL( @PageCurrent, 0) <1 SET @PageCurrent=1 IF ISNULL( @PageSize, 0) <1 SET @PageSize=10 IF ISNULL( @FieldShow,N '') =N '' SET @FieldShow=N '*' IF ISNULL( @FieldOrder,N '') =N '' SET @FieldOrder=N '' ELSE SET @FieldOrder=N 'ORDER BY '+LTRIM( @FieldOrder) IF ISNULL( @Where,N '') =N '' SET @Where=N '' ELSE SET @Where=N 'WHERE ('+@Where+N ')'![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL BEGIN DECLARE @sql nvarchar( 4000) SET @sql=N 'SELECT @PageCount=COUNT(*)' +N ' FROM '+@tbname +N ' '+@Where EXEC sp_executesql @sql,N '@PageCount int OUTPUT', @PageCount OUTPUT SET @PageCount=( @PageCount+@PageSize-1) /@PageSize END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --计算分页显示的TOPN值
DECLARE @TopN varchar( 20), @TopN1 varchar( 20) SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --第一页直接显示
IF @PageCurrent=1 EXEC(N 'SELECT TOP '+@TopN +N ' '+@FieldShow +N ' FROM '+@tbname +N ' '+@Where +N ' '+@FieldOrder) ELSE BEGIN --生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar( 4000), @s nvarchar( 1000) SELECT @Where1=N '', @s=@FieldKey WHILE CHARINDEX(N ',', @s) >0 SELECT @s=STUFF( @s, 1, CHARINDEX(N ',', @s),N ''), @Where1=@Where1 +N ' AND a.'+LEFT( @s, CHARINDEX(N ',', @s) -1) +N '='+LEFT( @s, CHARINDEX(N ',', @s) -1) SELECT @Where1=STUFF( @Where1+N ' AND a.'+@s+N '='+@s, 1, 5,N ''), @TopN=@TopN1-@PageSize![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --执行查询
EXEC(N 'SET ROWCOUNT '+@TopN1 +N ' SELECT '+@FieldKey +N ' INTO # FROM '+@tbname +N ' '+@Where +N ' '+@FieldOrder +N ' SET ROWCOUNT '+@TopN +N ' DELETE FROM #' +N ' SELECT '+@FieldShow +N ' FROM '+@tbname +N ' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1 +N ') '+@FieldOrder) END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--字符串缓存实现的通用分页存储过程(邹建)
CREATE PROC sp_PageView @tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar( 1000) ='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar( 1000) ='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) 用于指定排序顺序 @Where nvarchar( 1000) ='', --查询条件
@PageCount int OUTPUT --总页数
AS DECLARE @sql nvarchar( 4000) SET NOCOUNT ON --检查对象是否有效
IF OBJECT_ID( @tbname) IS NULL BEGIN RAISERROR(N '对象"%s"不存在', 1, 16, @tbname) RETURN END IF OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsTable') =0 AND OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsView') =0 AND OBJECTPROPERTY( OBJECT_ID( @tbname),N 'IsTableFunction') =0 BEGIN RAISERROR(N '"%s"不是表、视图或者表值函数', 1, 16, @tbname) RETURN END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --分页字段检查
IF ISNULL( @FieldKey,N '') ='' BEGIN RAISERROR(N '分页处理需要主键(或者惟一键)', 1, 16) RETURN END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --其他参数检查及规范
IF ISNULL( @PageCurrent, 0) <1 SET @PageCurrent=1 IF ISNULL( @PageSize, 0) <1 SET @PageSize=10 IF ISNULL( @FieldShow,N '') =N '' SET @FieldShow=N '*' IF ISNULL( @FieldOrder,N '') =N '' SET @FieldOrder=N '' ELSE SET @FieldOrder=N 'ORDER BY '+LTRIM( @FieldOrder) IF ISNULL( @Where,N '') =N '' SET @Where=N '' ELSE SET @Where=N 'WHERE ('+@Where+N ')'![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL BEGIN SET @sql=N 'SELECT @PageCount=COUNT(*)' +N ' FROM '+@tbname +N ' '+@Where EXEC sp_executesql @sql,N '@PageCount int OUTPUT', @PageCount OUTPUT SET @PageCount=( @PageCount+@PageSize-1) /@PageSize END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --计算分页显示的TOPN值
DECLARE @TopN varchar( 20), @TopN1 varchar( 20) SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --第一页直接显示
IF @PageCurrent=1 EXEC(N 'SELECT TOP '+@TopN +N ' '+@FieldShow +N ' FROM '+@tbname +N ' '+@Where +N ' '+@FieldOrder) ELSE BEGIN SELECT @PageCurrent=@TopN1, @sql=N 'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN +N ' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey +N ' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname +N ' '+@Where +N ' '+@FieldOrder SET ROWCOUNT @PageCurrent EXEC sp_executesql @sql, ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) N '@n int,@s nvarchar(4000) OUTPUT', @PageCurrent, @sql OUTPUT SET ROWCOUNT 0 IF @sql=N '' EXEC(N 'SELECT TOP 0' +N ' '+@FieldShow +N ' FROM '+@tbname) ELSE BEGIN SET @sql=STUFF( @sql, 1, 1,N '') --执行查询
EXEC(N 'SELECT TOP '+@TopN +N ' '+@FieldShow +N ' FROM '+@tbname +N ' WHERE '+@FieldKey +N ' IN('+@sql +N ') '+@FieldOrder) END END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --使用系统存储过程实现的通用分页存储过程(邹建) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) CREATE PROC sp_PageView ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @sql ntext, --要执行的sql语句 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @PageCurrent int=1, --要显示的页码 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @PageSize int=10, --每页的大小 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @PageCount int OUTPUT --总页数 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) AS ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET NOCOUNT ON ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) DECLARE @p1 int![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --初始化分页游标 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) EXEC sp_cursoropen ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @cursor=@p1 OUTPUT, ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @stmt=@sql, ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @scrollopt=1, ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @ccopt=1, ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @rowcount=@PageCount OUTPUT ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --计算总页数 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) IF ISNULL(@PageSize,0)<1 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET @PageSize=10 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET @PageCount=(@PageCount+@PageSize-1)/@PageSize ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET @PageCurrent=1 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ELSE ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET @PageCurrent=(@PageCurrent-1)*@PageSize+1 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --显示指定页的数据 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --关闭分页游标 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) EXEC sp_cursorclose @p1 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --根据分类表实现的分页存储过程(邹建) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --要分页的原始数据 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) CREATE TABLE tb( ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ID int PRIMARY KEY, --记录编号 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) grade varchar(10), --类别名称 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) uptime datetime) --更新时间 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) INSERT tb SELECT 1 ,'a','2004-12-11' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 2 ,'b','2004-12-11' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 3 ,'c','2004-12-11' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 4 ,'a','2004-12-12' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 5 ,'c','2004-12-13' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 6 ,'c','2004-12-13' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 7 ,'a','2004-12-14' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 8 ,'a','2004-12-15' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 9 ,'b','2004-12-16' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 10,'b','2004-12-17' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 11,'a','2004-12-17' ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --分页定义表 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) CREATE TABLE tb_Page( ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) grade varchar(10) PRIMARY KEY, --类别名称,与tb表的grade关联 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) Records int, --每页显示的记录数 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) Orders int) --在页中的显示顺序 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) INSERT tb_Page SELECT 'c',2,1 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 'b',1,2 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) UNION ALL SELECT 'a',2,3 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) GO ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --实现分页处理的存储过程 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) CREATE PROC p_PageView ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @PageCurrent int=1 --要显示的当前页码 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) AS ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET NOCOUNT ON ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --得到每页的记录数 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) DECLARE @PageSize int![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SELECT @PageSize=SUM(Records) FROM tb_Page ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) IF ISNULL(@PageSize,0)<0 RETURN ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --分页显示处理 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET @PageCurrent=@PageCurrent*@PageSize ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET ROWCOUNT @PageCurrent ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SELECT SID=IDENTITY( int,1,1),ID ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) INTO # FROM( ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SELECT TOP 100 PERCENT a.ID ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) FROM tb a ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) LEFT JOIN tb_Page b ON a.grade=b.grade ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ORDER BY CASE WHEN b.grade IS NULL THEN 1 ELSE 0 END,--分类没有定义的显示在最后 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ((SELECT COUNT(*) FROM tb ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) WHERE grade=a.grade ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) AND (uptime>a.uptime OR uptime=a.uptime AND id>=a.id))-1) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) /b.Records, ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) b.Orders,a.ID DESC)a ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) IF @PageCurrent>@PageSize ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) BEGIN ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET @PageCurrent=@PageCurrent-@PageSize ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET ROWCOUNT @PageCurrent ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) DELETE FROM # ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) END ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SELECT a.* FROM tb a,# b ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) WHERE a.ID=b.ID ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ORDER BY b.SID ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) GO ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --调用 ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) EXEC p_PageView 2 /*--结果
ID grade uptime
----------- ---------- ------------------------------------------------------
3 c 2004-12-11 00:00:00.000
9 b 2004-12-16 00:00:00.000
7 a 2004-12-14 00:00:00.000
4 a 2004-12-12 00:00:00.000
2 b 2004-12-11 00:00:00.000
--*/![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
还有一个,论坛里找的,不是老大的:
CREATE PROCEDURE SP_Page ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) @TB VARCHAR( 50), @COL VARCHAR( 50), -- @COLTYPE INT,
@ORDERBY BIT, @COLLIST VARCHAR( 800), @PAGESIZE INT, @PAGE INT, @CONDITION VARCHAR( 800), @RecPages INT, @RecCount INT OUTPUT, @PAGES INT OUTPUT, @OUTSQL NVARCHAR( 4000) OUTPUT ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) AS![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) DECLARE @SQL NVARCHAR( 4000) DECLARE @WHERE1 VARCHAR( 800) DECLARE @WHERE2 VARCHAR( 800) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) IF @CONDITION IS NULL OR RTRIM( @CONDITION) = '' BEGIN SET @WHERE1=' WHERE ' SET @WHERE2=' ' END ELSE BEGIN SET @WHERE1=' WHERE ('+@CONDITION+') AND ' SET @WHERE2=' WHERE ('+@CONDITION+') ' END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST( @PAGESIZE AS VARCHAR) +') FROM '+@TB+@WHERE2![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) IF @RecPages = 0 EXEC SP_EXECUTESQL @SQL,N '@PAGES INT OUTPUT,@RecCount INT OUTPUT', @PAGES OUTPUT, @RecCount OUTPUT ELSE SELECT @PAGES = @RecPages![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) IF @ORDERBY=0 SET @SQL= 'SELECT TOP '+CAST( @PAGESIZE AS VARCHAR) +' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'>(SELECT MAX('+@COL+') '+' FROM (SELECT TOP '+CAST( @PAGESIZE*( @PAGE-1) AS VARCHAR) +' '+ @COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+') t) ORDER BY '+@COL ELSE SET @SQL='SELECT TOP '+CAST( @PAGESIZE AS VARCHAR) +' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'<(SELECT MIN('+@COL+') '+' FROM (SELECT TOP '+CAST( @PAGESIZE*( @PAGE-1) AS VARCHAR) +' '+ @COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+' DESC) t) ORDER BY '+@COL+' DESC'![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) IF @PAGE=1 SET @SQL='SELECT TOP '+CAST( @PAGESIZE AS VARCHAR) +' '+@COLLIST+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC' END![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) SET @OUTSQL = @SQL![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) EXEC( @SQL) GO
|