分页

Question

我写了一个分页的存储过程,一直不知道怎么改才能够支持多表联合查询,并且支持where条件里指定多个条件, 支持select结果里指定返回列的集合下面是我写的
create proc proc_getPageRecords
@tableName nvarchar(100), --表名
@colNameForQuery nvarchar(100), --where条件
@selectValue nvarchar(100),     --where值
@Page int=1,   --返回第几页
@perPageRecords int=10,  --每页返回的行
@total_pages int output  --总页数as
declare @total_records int
declare @sql nvarchar(max)
set @sql=N'select @rows=count(*) from ' +@tableName+ N' where '  +@colNameForQuery+ N' like ''%'+@selectValue+N'%'''
--print(@sql)
exec sp_executesql @sql,N'@rows int output',@total_records output
--select @total_records
set @total_pages=(@total_records+(@perPageRecords-1))/@perPageRecords
--print(@total_pages)
declare @not_show_records int
set @not_show_records=@total_records-(@page-1)*@perPageRecords
exec('select top '+@perPageRecords+' * from (select top '+@not_show_records+' * from dbo.Users where UserName like''%'+@selectValue+'%'' order by UserId desc) as tmp order by tmp.UserId asc')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GOdeclare @total_pages int
--set @total_pages=0--测试
exec proc_getPageRecords 'dbo.Users','UserName','test',40,10,@total_pages out
print('------------')
print(@total_pages)
print('------------')
exec proc_getPageRecords 'dbo.Users','UserName','testaaa',2,10,@total_pages out
print(@total_pages)

Answer 1

1------------------------------------------------
--游标不是明智的选择，在小数据量时可以使用
create     procedure   hahaha   
  @sqlstr   nvarchar(4000),   --查询字符串   
  @currentpage   int, --第N页   
  @pagesize   int --每页行数   
  as   
  set   nocount   on   
  declare   @P1   int, --P1是游标的id   
  @rowcount   int   
  exec   sp_cursoropen   @P1   output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount   output   
  select   ceiling(1.0*@rowcount/@pagesize)   as   总页数--,@rowcount   as   总行数,@currentpage   as   当前页   
  set   @currentpage=(@currentpage-1)*@pagesize+1   
  exec   sp_cursorfetch   @P1,16,@currentpage,@pagesize     
  exec   sp_cursorclose   @P1   
  set   nocount   off   
    
  测试:   hahaha   '任何条件的SQL语句',2,10   
  
  
  2----------------
  
  
  CREATE   PROCEDURE   GetRecordFromPage   
          @tblName             varchar(255),               --   表名   
          @fldName             varchar(255),               --   字段名   
          @PageSize           int   =   10,                       --   页尺寸   
          @PageIndex         int   =   1,                         --   页码   
          @IsCount             bit   =   0,                         --   返回记录总数,   非   0   值则返回   
          @OrderType         bit   =   0,                         --   设置排序类型,   非   0   值则降序   
          @strWhere           varchar(1000)   =   ''     --   查询条件   (注意:   不要加   where)   
  AS   
    
  declare     @strSQL       varchar(1000)           --   主语句   
  declare   @strTmp       varchar(300)           --   临时变量   
  declare   @strOrder   varchar(400)               --   排序类型   
    
  if   @OrderType   !=   0   
  begin   
          set   @strTmp   =   "<(select   min"   
          set   @strOrder   =   "   order   by   ["   +   @fldName   +"]   desc"   
  end   
  else   
  begin   
          set   @strTmp   =   ">(select   max"   
          set   @strOrder   =   "   order   by   ["   +   @fldName   +"]   asc"   
  end   
    
  set   @strSQL   =   "select   top   "   +   str(@PageSize)   +   "   *   from   ["   
          +   @tblName   +   "]   where   ["   +   @fldName   +   "]"   +   @strTmp   +   "(["   
          +   @fldName   +   "])   from   (select   top   "   +   str((@PageIndex-1)*@PageSize)   +   "   ["   
          +   @fldName   +   "]   from   ["   +   @tblName   +   "]"   +   @strOrder   +   ")   as   tblTmp)"   
          +   @strOrder   
    
  if   @strWhere   !=   ''   
          set   @strSQL   =   "select   top   "   +   str(@PageSize)   +   "   *   from   ["   
                  +   @tblName   +   "]   where   ["   +   @fldName   +   "]"   +   @strTmp   +   "(["   
                  +   @fldName   +   "])   from   (select   top   "   +   str((@PageIndex-1)*@PageSize)   +   "   ["   
                  +   @fldName   +   "]   from   ["   +   @tblName   +   "]   where   ("   +   @strWhere   +   ")   "   
                  +   @strOrder   +   ")   as   tblTmp)   and   ("   +   @strWhere   +   ")   "   +   @strOrder   
    
  if   @PageIndex   =   1   
  begin   
          set   @strTmp   =   ""   
          if   @strWhere   !=   ''   
                  set   @strTmp   =   "   where   ("   +   @strWhere   +   ")"   
    
          set   @strSQL   =   "select   top   "   +   str(@PageSize)   +   "   *   from   ["   
                  +   @tblName   +   "]"   +   @strTmp   +   "   "   +   @strOrder   
  end   
    
  if   @IsCount   !=   0   
          set   @strSQL   =   "select   count(*)   as   Total   from   ["   +   @tblName   +   "]"   
    
  exec   (@strSQL)   
  GO   

Answer 2

还有一个问题在    by   UserId   desc)   as   tmp   order   by   tmp.UserId   asc')能不能根据参数里的表名字,找出主键用于排序? 而不是这样写固定的或传递一个参数用于排序

Answer 3

发表于：2007-11-15 23:56:259楼 得分:0 
SQL codeCREATE   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 
create table ta(col1 int,col2 char(2))
go
insert ta select 1,'2'exec p_show '(select * from ta)',10,1,'col1,col2','col2'
drop table ta
drop proc p_show/*col1        col2 
----------- ---- 
1           2 （所影响的行数为 1 行）
*/ 
 
 
 
 --TOP n 实现的通用分页排序存储过程
--exec sp_PageView t,'ID',1,5,'ID,sex,Name','ID asc','ID=1',1
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
GO