分页存储过程的问题。

Question

现在使用的2个分页存储过程都有点问题，不知道怎么修改，大家帮忙看看。第一个：问题当排序字段中出现多个重复的时候，就会导致数据无法读取了。例如按照price排序，但是价格都是30，只有1个是31的。 这个时候只有第一页有数据，其他页面都没数据了。ALTER   PROCEDURE [dbo].[xt_pagesplit]
@tbname sysname, --要分页显示的表名
@fieldkey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@where nvarchar(1000), --查询条件
@fieldshow nvarchar(1000), --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@fieldOrder nvarchar(1000), ----以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC  --用于指定排序顺序@PageCurrent  int=1,          --要显示的页码
@PageSize    int=30,         --每页的大小(记录数)
@PageCount   int=0 OUTPUT,     --总页数
@ItemCount int=0 output --数据总数
ASDECLARE @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 @ItemCount = @PageCount
SET @PageCount=ceiling((@PageCount+@PageSize-1)/@PageSize)
if(@pagecount=0)
set @pagecount=1
--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' '+@FieldOrderSET 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第二个：如果出现在页面底部有重复过多的相同排序，第二页也会出现这个商品。不知道什么问题。ALTER PROCEDURE [dbo].[xt_Common_PagingLarge]
@tbname VARCHAR (200),
@fieldkey VARCHAR (100),
@where VARCHAR (4000)='', 
@fieldshow VARCHAR (2000),
@fieldOrder VARCHAR (4000)='',
@PageCurrent INT=1, 
@PageSize INT=30, 
--@Group VARCHAR (4000)='', 
@PageCount   int=0 OUTPUT,     --总页数
@ItemCount int=0 output --数据总数
AS
set @PageCurrent = (@PageCurrent-1) * @PageSize
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @DeclareSortType VARCHAR(4000)
DECLARE @SortColumnBegin VARCHAR(4000)
DECLARE @SortColumnWhere VARCHAR(4000)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
    Declare @Group varchar(4000)
set @Group = ''
SET @DeclareSortType =''
SET @SortColumnBegin = ''
SET @SortColumnWhere =''
IF @fieldshow = ''
SET @fieldshow = '*'
IF @where = ''
SET @where = 'Where 1=1'
ELSE
SET @where = 'Where ' +    @where
IF @Group <>''
SET @Group = 'GROUP BY ' + @Group
IF @fieldOrder = ''
SET @fieldOrder = @fieldkey + ' ASC'
    DECLARE @PageCurrents INT
    SET @PageCurrents = @PageCurrent
    IF @PageCurrents<=0
    SET @PageCurrents = 0
DECLARE @equalOperator char(2)
    IF @PageCurrents=0
    BEGIN
      SET @equalOperator = '='
      SET @PageCurrents = 1
    END
    ELSE
        SET @equalOperator = '' DECLARE @pos1 INT, @pos2 INT,@Depth INT
DECLARE @SubOrder VARCHAR(200),@EndOrder VARCHAR(2000),@PreOrder VARCHAR(2000),@PreTable VARCHAR(2000),@PreSortColumn VARCHAR(2000)
SET @EndOrder = ''
SET @Depth = 1
SET @EndOrder = @fieldOrder
SET @pos1 = 1--开始计算分页数据
declare @sql nvarchar(3000)
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUTset @ItemCount = @PageCount
SET @PageCount=ceiling((@PageCount+@PageSize-1)/@PageSize)
if(@pagecount=0)
set @pagecount=1
--循环开始
WHILE @pos1 > 0
BEGIN
SET @pos1 = CHARINDEX(',', @EndOrder)
IF @pos1>0
SET @SubOrder = SUBSTRING(@EndOrder, 1, @pos1-1)
ELSE
SET @SubOrder = @EndOrder
SET @EndOrder = SUBSTRING(@EndOrder, @pos1+1, len(@EndOrder)-@pos1)
SET @SubOrder = REPLACE(REPLACE(@SubOrder, ' asc', ' ASC'), ' desc', ' DESC')
IF CHARINDEX(' DESC', @SubOrder) > 0
IF CHARINDEX(' ASC', @SubOrder) > 0
BEGIN
IF CHARINDEX(' DESC', @SubOrder) < CHARINDEX(' ASC', @SubOrder)
SET @Operator = '<'
ELSE
SET @Operator = '>'
END
ELSE
SET @Operator = '<'
ELSE
SET @Operator = '>'
      
SET @SortColumn = REPLACE(REPLACE(REPLACE(@SubOrder, ' ASC', ''), ' DESC', ''), ' ', '')
SET @pos2 = CHARINDEX('.', @SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable = @tbname
SET @SortName = @SortColumn
END
DECLARE @type varchar(50)
DECLARE @prec int
Select @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype
Where o.name = @SortTable AND c.name = @SortName
IF @type IS NULL
    BEGIN
       DECLARE @posTableName1 INT, @posTableName2 INT,@tbnameubStr varchar(200)
       SET @posTableName1 = CHARINDEX(' ' + @SortTable + ' ', @tbname)
       IF @posTableName1 > 0
          BEGIN
              SET @tbnameubStr = SUBSTRING(@tbname,1,@posTableName1-1)
              SET @tbnameubStr = LTRIM(@tbnameubStr)
              SET @tbnameubStr = RTRIM(@tbnameubStr)
              
              SET @posTableName2 = CHARINDEX(' ', @tbnameubStr)
              WHILE @posTableName2 > 0
                  BEGIN
                       SET @tbnameubStr = SUBSTRING(@tbnameubStr,@posTableName2,LEN(@tbnameubStr)-1)
                       SET @tbnameubStr = LTRIM(@tbnameubStr)
                       SET @tbnameubStr = RTRIM(@tbnameubStr)
                       SET @posTableName2 = CHARINDEX(' ', @tbnameubStr)
                  END
          END
          Select @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype
         Where o.name = @tbnameubStr AND c.name = @SortName
        --ELSE
          --SET @tbnameubStr = @tbname
    END
IF CHARINDEX('char', @type) > 0
BEGIN
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
END
SET @DeclareSortType = @DeclareSortType + '
DECLARE @SortColumnBegin' + @SortName + ' ' + @type

IF @Depth <> 1
BEGIN
SET @SortColumnBegin = @SortColumnBegin + ','  + '@SortColumnBegin' + @SortName + '=' + @SortColumn
IF @pos1>0
SET @SortColumnWhere = @SortColumnWhere + ' OR ('+@PreSortColumn+'=@SortColumnBegin' + @PreOrder + ' AND ' +  @SortColumn + @Operator + '@SortColumnBegin' + @SortName + ')'
ELSE
SET @SortColumnWhere = @SortColumnWhere + ' OR ('+@PreSortColumn+'=@SortColumnBegin' + @PreOrder + ' AND ' +  @SortColumn + @Operator +  @equalOperator + '@SortColumnBegin' + @SortName + ')'
END                    
ELSE
BEGIN
SET @SortColumnBegin = @SortColumnBegin + '@SortColumnBegin' + @SortName + '=' + @SortColumn 
IF @pos1>0
SET @SortColumnWhere = @SortColumnWhere + @SortColumn + @Operator + '@SortColumnBegin' + @SortName
ELSE
SET @SortColumnWhere = @SortColumnWhere + @SortColumn + @Operator + @equalOperator + '@SortColumnBegin' + @SortName
END 
SET @Depth = @Depth +1
SET @PreOrder = @SortName
SET @PreTable = @SortTable
SET @PreSortColumn = @SortColumn
END    
    EXEC('
          ' + @DeclareSortType + '
          SET ROWCOUNT ' + @PageCurrents + '
          Select ' + @SortColumnBegin + ' FROM ' + @tbname + ' ' + @where + ' ' + @Group + ' ORDER BY ' + @fieldOrder + '
          SET ROWCOUNT ' + @PageSize + '
          Select ' + @fieldshow + ' FROM    ' + @tbname + ' ' + @where    + ' AND (' + @SortColumnWhere + ') ' + @Group + ' ORDER BY ' + @fieldOrder + '    
      ')
END

Answer 1

用這個吧/*--用存储过程实现的分页程序 显示指定表、视图、查询结果的第X页
 对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
 如果视图或查询结果中有主键,不推荐此方法--邹建 2003.09(引用请保留此信息)--*//*--调用示例
 exec p_show '地区资料' exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'
--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GOCREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10,   --每页的大小(行数)
@PageCurrent int=1,   --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
 ,@Obj_ID int    --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
 ,@strjoin nvarchar(4000) --连接字段
 ,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
 ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
 ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
 ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果显示第一页，可以直接用top来完成
if @PageCurrent=1 
begin
 select @Id1=cast(@PageSize as varchar(20))
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
 return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
 select @Id1=cast(@PageSize as varchar(20))
  ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
 if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键
 begin
  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
   goto lbusetemp  --如果表中无主键,则用临时表处理  select @FdName=name from syscolumns where id=@Obj_ID and colid in(
   select colid from sysindexkeys where @Obj_ID=id and indid in(
    select indid from sysindexes where @Obj_ID=id and name in(
     select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
   )))
  if @@rowcount>1  --检查表中的主键是否为复合主键
  begin
   select @strfd='',@strjoin='',@strwhere=''
   select @strfd=@strfd+',['+name+']'
    ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
    ,@strwhere=@strwhere+' and b.['+name+'] is null'
    from syscolumns where id=@Obj_ID and colid in(
    select colid from sysindexkeys where @Obj_ID=id and indid in(
     select indid from sysindexes where @Obj_ID=id and name in(
      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
    )))
   select @strfd=substring(@strfd,2,2000)
    ,@strjoin=substring(@strjoin,5,4000)
    ,@strwhere=substring(@strwhere,5,4000)
   goto lbusepk
  end
 end
end
else
 goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity: 
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr
  +' where '+@FdName+' not in(select top '
  +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
  +')'+@FdOrder
  )
 return/*--表中有复合主键的处理方法--*/
lbusepk:  
 exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
  (select top 100 percent * from '+@QueryStr+@FdOrder+') a
  left join (select top '+@Id2+' '+@strfd+' 
  from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
  where '+@strwhere+') a'
  )
 return/*--用临时表处理的方法--*/
lbusetemp:  
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
 ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
 ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
  into #tb from'+@QueryStr+@FdOrder+'
 select '+@FdShow+' from #tb where '+@FdName+' between '
 +@Id1+' and '+@Id2
 )GO

Answer 2

SQL2005以上版本可用Row_Number處理分頁樓主查原因，把參數貼出來，說明主健列

Answer 3

我在排序的时候输入的是price desc,id desc ，id是主键，不明白为什么获取不到数据呢，如果单独用ID排序的话，就没问题。如果Price也是不同的数字，也没问题，就是当出现很多一样Price的时候，就会出错。

Answer 4

把執行結果集的段 用select 換exec ...看看生成語句格式就明原因了。如果是SQL05參照2樓 row_number