吴旗娃师傅是谁?邹建的有几个,给你.--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 用于指定排序顺序 @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--分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键(或者惟一键)',1,16) RETURN END--其他参数检查及规范 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')'--如果@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--计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=(@PageCurrent-1)*@PageSize--第一页直接显示 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.*' --生成主键(惟一键)处理条件 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')' --执行查询 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-- 字符串缓存实现的通用分页存储过程(转自邹建) CREATE PROC sp_PageView @tbname sysname, --要分页显示的表名 @FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小(记录数) @FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序 @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--分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键(或者惟一键)',1,16) RETURN END--其他参数检查及规范 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')'--如果@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--计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize --第一页直接显示 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, 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--使用系统存储过程实现的通用分页存储过程(转自邹建) CREATE PROC sp_PageView @sql ntext, --要执行的sql语句 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小 @PageCount int OUTPUT --总页数 AS SET NOCOUNT ON DECLARE @p1 int --初始化分页游标 EXEC sp_cursoropen @cursor=@p1 OUTPUT, @stmt=@sql, @scrollopt=1, @ccopt=1, @rowcount=@PageCount OUTPUT--计算总页数 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount SET @PageCurrent=1 ELSE SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据 EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标 EXEC sp_cursorclose @p1
--转的 create proc p_1 @pageSize int = 10, ----每页显示的记录个数 @page int = 1, ----要显示那一页的记录 @Counts int = 1 output ----查询到的记录数 as select *,identity(int,1,1) as 'id' into # from tb set rowcount @pagesize select * from # where id>(@page-1)*@pagesize set @counts=@@rowcount set rowcount 0 go
汗。。吴旗娃是咱csdn的高手,aspnetpager的创始人楼主代码已经发了,记的结贴给分啊
IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) BEGIN IF @PageIndex = 1 --返回第一页数据 BEGIN SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1 END IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据 BEGIN SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 END END ELSE
BEGIN IF @SortType = 1 --仅主键正序排序 BEGIN IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 + ' ) AS TMP ' + @new_order1 END END IF @SortType = 2 --仅主键反序排序 BEGIN IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey +' FROM '+ @TableName + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 + ' ) AS TMP ' + @new_order1 END END IF @SortType = 3 --多列排序,必须包含主键,且放置最后,否则不处理 BEGIN IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0 BEGIN PRINT('ERR_02') RETURN END IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP ' + @new_order2 + ' ) AS TMP ' + @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 + ' ) AS TMP ' + @new_order1 END END END PRINT(@SQL) EXEC(@Sql)
sql测试: --测试数据 --select count(*) from T_TEST Create TABLE T_TEST(ID INT IDENTITY(1,1) PRIMARY KEY,CLASS_A VARCHAR(50),CLASS_B VARCHAR(50),ADD_DT DATETIME) DECLARE @I INT Select @I = 1 WHILE @I < = 999998 BEGIN Insert INTO T_TEST(CLASS_A,CLASS_B,ADD_DT) Select CASE @I%2 WHEN 0 THEN 'CLASS_A' + CAST(@I AS VARCHAR) ELSE CAST(@I AS VARCHAR) END, CASE @I%5 WHEN 0 THEN 'CLASS_B' + CAST(@I AS VARCHAR) ELSE CAST(@I AS VARCHAR) END, GETDATE() Select @I = @I + 1 END Create INDEX _INDEX ON T_TEST(add_dt,id) 使用: 以多列排序为例 第一页查询, 第一页取出总记录和总页数,以后就不要从数据库里取了 DECLARE @T1 DATETIME,@T2 DATETIME DECLARE @A INT,@B INT Select @T1 = GETDATE() EXEC P_viewPage 'T_TEST','ID,ADD_DT','id','','add_dt asc,id asc',3,0,10,1,@A out,@B out Select @T2 = GETDATE() Select CAST(DATEDIFF(ms,@T1,@T2) AS VARCHAR)+'ms' Select @A,@B 其它页(如第1161页): DECLARE @T1 DATETIME,@T2 DATETIME DECLARE @A INT,@B INT Select @T1 = GETDATE() EXEC P_viewPage 'T_TEST','ID,ADD_DT','id','','add_dt asc,id asc',3,999998,10,1161,null,null Select @T2 = GETDATE() Select CAST(DATEDIFF(ms,@T1,@T2) AS VARCHAR)+'ms' --Select @A,@B
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--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)
RETURN
END--其他参数检查及规范
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')'--如果@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--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=(@PageCurrent-1)*@PageSize--第一页直接显示
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.*' --生成主键(惟一键)处理条件
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')' --执行查询
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-- 字符串缓存实现的通用分页存储过程(转自邹建)
CREATE PROC sp_PageView
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@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--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)
RETURN
END--其他参数检查及规范
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')'--如果@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--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize --第一页直接显示
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,
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--使用系统存储过程实现的通用分页存储过程(转自邹建)
CREATE PROC sp_PageView
@sql ntext, --要执行的sql语句
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标
EXEC sp_cursorclose @p1
create proc p_1
@pageSize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@Counts int = 1 output ----查询到的记录数
as
select *,identity(int,1,1) as 'id' into # from tb
set rowcount @pagesize
select * from # where id>(@page-1)*@pagesize
set @counts=@@rowcount
set rowcount 0
go
BEGIN
IF @PageIndex = 1 --返回第一页数据
BEGIN
SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 2 --仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+' FROM '+ @TableName
+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 3 --多列排序,必须包含主键,且放置最后,否则不处理
BEGIN
IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
BEGIN PRINT('ERR_02') RETURN END
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
+ @new_order2 + ' ) AS TMP ' + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1 + ' ) AS TMP ' + @new_order1
END
END
END
PRINT(@SQL)
EXEC(@Sql)
--测试数据
--select count(*) from T_TEST
Create TABLE T_TEST(ID INT IDENTITY(1,1) PRIMARY KEY,CLASS_A VARCHAR(50),CLASS_B VARCHAR(50),ADD_DT DATETIME)
DECLARE @I INT
Select @I = 1
WHILE @I < = 999998
BEGIN
Insert INTO T_TEST(CLASS_A,CLASS_B,ADD_DT)
Select CASE @I%2 WHEN 0 THEN 'CLASS_A' + CAST(@I AS VARCHAR) ELSE CAST(@I AS VARCHAR) END,
CASE @I%5 WHEN 0 THEN 'CLASS_B' + CAST(@I AS VARCHAR) ELSE CAST(@I AS VARCHAR) END, GETDATE()
Select @I = @I + 1
END
Create INDEX _INDEX ON T_TEST(add_dt,id)
使用: 以多列排序为例
第一页查询,
第一页取出总记录和总页数,以后就不要从数据库里取了
DECLARE @T1 DATETIME,@T2 DATETIME
DECLARE @A INT,@B INT
Select @T1 = GETDATE()
EXEC P_viewPage 'T_TEST','ID,ADD_DT','id','','add_dt asc,id asc',3,0,10,1,@A out,@B out
Select @T2 = GETDATE()
Select CAST(DATEDIFF(ms,@T1,@T2) AS VARCHAR)+'ms'
Select @A,@B
其它页(如第1161页):
DECLARE @T1 DATETIME,@T2 DATETIME
DECLARE @A INT,@B INT
Select @T1 = GETDATE()
EXEC P_viewPage 'T_TEST','ID,ADD_DT','id','','add_dt asc,id asc',3,999998,10,1161,null,null
Select @T2 = GETDATE()
Select CAST(DATEDIFF(ms,@T1,@T2) AS VARCHAR)+'ms'
--Select @A,@B