求分页的存储过程

Question

转贴：
这个很不错的，看看行吗？
CREATE PROCEDURE sp_page
  @tb         varchar(20), --表名
  @col        varchar(20), --按该列来进行分页
  @coltype    bit,         --@col列的类型,0-数字类型,1-字符类型
  @collist    varchar(800),--要查询出的字段列表
  @selecttype int,         --查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页
  @pagesize   int,         --每页记录数
  @page       int,         --指定页
  @minid      varchar(50), --当前最小号
  @maxid      varchar(50)  --当前最大号
AS
DECLARE @sql varchar(8000)
IF @coltype=1
BEGIN
  SET @minid=''''+@minid+''''
  SET @maxid=''''+@maxid+''''
END
SET @sql=
  CASE @selecttype
    WHEN 1--前页
    THEN 'SELECT '+@collist+' FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
         ' '+@collist+' FROM '+@tb+' WHERE '+@col+'<'+@minid+
         ' ORDER BY '+@col+' DESC) t ORDER BY '+@col
    WHEN 2--后页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+' WHERE '+@col+'>'+@maxid+' ORDER BY '+@col
    WHEN 3--首页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+
         ' '+@collist+' FROM '+@tb+' ORDER BY '+@col
    WHEN 4--末页
    THEN 'SELECT '+@collist+' FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
          ' '+@collist+' FROM '+@tb+' ORDER BY '+@col+' DESC) t ORDER BY '+@col
    WHEN 5--指定页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+' WHERE '+@col+' NOT IN'+
         '(SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+
         ' FROM '+@tb+' ORDER BY '+@col+') ORDER BY '+@col
  END
EXEC(@sql)
GO
可以指定表名，指定输出列，不管分页的字段是数字类型还是字符类型，均实用
可以输出前页,后页,首页,末页,指定页
调用方法：
exec sp_test 'tablename','id',0,'*',5,10,2,'20','29'

Answer 1

转贴：
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
         查询可以指定页大小、指定查询任意页、指定输出字段列表
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
DECLARE @i int,@id varchar(50)
IF @coltype=1 or @coltype=2--字段类型为字符或日期时间要加上引号以作比较用
BEGIN
  SET @minid=''''+@minid+''''
  SET @maxid=''''+@maxid+''''
END
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
SET @sql=
  CASE @selecttype
    WHEN 1--前页
    THEN 'SELECT * FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
         ' '+@collist+' FROM '+@tb+@where1+@col+
         CASE @orderby WHEN 0 THEN '<'+@minid ELSE '>'+@maxid END+
         ' ORDER BY '+@col+CASE @orderby WHEN 0 THEN ' DESC' ELSE '' END+
         ') t ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE 'DESC' END
    WHEN 2--后页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+@where1+@col+
         CASE @orderby WHEN 0 THEN '>'+@maxid ELSE '<'+@minid END+
         ' ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
    WHEN 3--首页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+@where2+'ORDER BY '+@col+
         CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
    WHEN 4--末页
    THEN 'SELECT * FROM (SELECT TOP '+CAST(@pagesize AS varchar)+' '+
         @collist+' FROM '+@tb+@where2+'ORDER BY '+@col+
         CASE @orderby WHEN 0 THEN ' DESC' ELSE '' END+') t ORDER BY '+
         @col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
  END
IF @selecttype>=1 and @selecttype<=4
BEGIN
  EXEC(@sql)
  RETURN
END
ELSE
BEGIN--指定页
  IF @coltype=1
    IF @orderby =0
      SET @id=''''''
    ELSE
      SET @id=''''+CHAR(255)+''''
  ELSE
    IF @coltype=2
      IF @orderby =0
        SET @id='''1753-1-1'''
      ELSE
        SET @id='''9999-12-31'''
    ELSE
      IF @orderby =0
        SET @id='-2147483648'
      ELSE
        SET @id='2147483647'
  SET @i=0
  --为减少之后SELECT TOP ...的数据量,此处每10000条循环一次,以尽可能接近所查询页
  WHILE @i<@pagesize*@page
  BEGIN
    IF @i+10000<@pagesize*@page
    BEGIN
      IF @orderby=0
        SET @sql='SELECT @id=CASE '+CAST(@coltype AS varchar)+
          ' WHEN 1 THEN ''''''''+CAST(MAX('+@col+') AS varchar(50))+'+
          ''''''''''+
          ' WHEN 2 THEN ''''''''+CONVERT(char(23),MAX('+@col+'),121)+'+
          ''''''''''+
          ' ELSE CAST(MAX('+@col+') AS varchar) END FROM (SELECT TOP 10000 '+
          @col+' FROM '+@tb+@where1+@col+'>'+@id+' ORDER BY '+@col+') t'
      ELSE
        SET @sql='SELECT @id=CASE '+CAST(@coltype AS varchar)+
          ' WHEN 1 THEN ''''''''+CAST(MIN('+@col+') AS varchar(50))+'+
          ''''''''''+
          ' WHEN 2 THEN ''''''''+CONVERT(char(23),MIN('+@col+'),121)+'+
          ''''''''''+
          ' ELSE CAST(MIN('+@col+') AS varchar) END FROM (SELECT TOP 10000 '+
          @col+' FROM '+@tb+@where1+@col+'<'+@id+' ORDER BY '+@col+' DESC) t'
      EXEC sp_executesql @sql,N'@id varchar(50) OUTPUT',@id OUTPUT
      SET @i=@i+10000
      IF @i+10000>=@pagesize*@page
        BREAK
    END
    ELSE
      BREAK
  END
  --上面的循环保证下面的子查询最多只有10000条数据
  IF @orderby=0
    SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
             ' FROM '+@tb+@where1+@col+'>'+@id+' AND '+@col+' NOT IN'+
             '(SELECT TOP '+CAST(@pagesize*(@page-1)-@i AS varchar)+
             ' '+@col+' FROM '+@tb+@where1+@col+'>'+@id+' ORDER BY '+@col+
             ') ORDER BY '+@col
  ELSE
    SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
             ' FROM '+@tb+@where1+@col+'<'+@id+' AND '+@col+' NOT IN'+
             '(SELECT TOP '+CAST(@pagesize*(@page-1)-@i AS varchar)+
             ' '+@col+' FROM '+@tb+@where1+@col+'<'+@id+' ORDER BY '+@col+
             ' DESC) ORDER BY '+@col+' DESC'
  EXEC(@sql)
END
GO--测试事例
select identity(int,1,1) id,getdate() dt,xx=cast('' as varchar(10)) into #t
 from sysobjects
update #t set dt=dateadd(day,id-200,dt),
              xx='xxxx'+right('000000'+cast(id as varchar(10)),6)
--exec sp_page '#t','id',0,0,'*',5,10,3,'','',''--按id顺序取第三页
--exec sp_page '#t','id',0,1,'*',5,10,3,'','',''--按id倒序取第三页
--exec sp_page '#t','xx',1,0,'*',1,10,3,'xxxx000021','xxxx000030',''--按xx顺序取前一页
--exec sp_page '#t','xx',1,1,'*',2,10,3,'xxxx000134','xxxx000143',''--按xx倒序取后一页
--exec sp_page '#t','dt',2,0,'*',4,10,3,'','',''--按dt顺序取最后一页
exec sp_page '#t','dt',2,1,'*',3,10,3,'','',''--按dt倒序取首页
drop table #t

Answer 2

@selecttype int,         --查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页  @minid      varchar(50), --当前最小号
  @maxid      varchar(50)  --当前最大号这三个参数有何意义？