求一分页SQL存储过程

Question

--建表CREATE TABLE TbA (ID int,UserName CHAR(10),Grade int)
INSERT INTO TbA (ID,UserName,Grade)
SELECT 1,'A',0
UNION SELECT 2,'B',1
UNION SELECT 3,'C',2CREATE TABLE TbB (ID int,Userid int,uptime DATETIME)
INSERT INTO TbB (ID,Userid,uptime)
SELECT 1,3,GETDATE()
UNION SELECT 1,3,GETDATE()
UNION SELECT 2,1,GETDATE()
UNION SELECT 3,2,GETDATE()
UNION SELECT 4,1,GETDATE()
UNION SELECT 5,2,GETDATE()
UNION SELECT 6,3,GETDATE()
UNION SELECT 7,1,GETDATE()--创建存储过程CREATE PROC pagination
@pagesize int, --每页大小
@pagecount int, --第几页
@tblname VARCHAR(10), --表名
@keyname VARCHAR(10) --主键列
AS
DECLARE @SQL varchar(8000)
SET @SQL='SELECT TOP '+ CAST(@pagesize AS VARCHAR)+' * FROM '+@tblname+ ' WHERE '+@keyname+' NOT IN (SELECT TOP '
          +CAST(@pagesize*(@pagecount-1) AS VARCHAR)+' '+@keyname+' FROM '+@tblname+')'
EXEC(@SQL)
GO--建立临时表SELECT TbB.ID,TbA.UserName,TbB.uptime,TbA.Grade INTO #temp FROM TbA,TbB WHERE TbB.Userid=TbA.ID--测试1(显示第一页)EXEC pagination 4,1,'#temp','ID'--结果ID      UserName    uptime                   grade
1C          2004-12-17 16:45:41.4502
2A          2004-12-17 16:45:41.4500
3B          2004-12-17 16:45:41.4501
4A          2004-12-17 16:45:41.4500--测试2(显示第二页)EXEC pagination 4,2,'#temp','ID'--结果ID      UserName   uptime                    grade
5B         2004-12-17 16:45:41.4501
6C         2004-12-17 16:45:41.4502
7A         2004-12-17 16:45:41.4500

Answer 1

一个将数据分页的存储过程     选择自 pbsql 的 Blog  
关键字   一个将数据分页的存储过程 
出处    
 
 CREATE PROCEDURE sp_page
  @tb         varchar(50), --表名
  @col        varchar(50), --按该列来进行分页
  @coltype    int,         --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
  @orderby    bit,         --排序,0-顺序,1-倒序
  @collist    varchar(800),--要查询出的字段列表,*表示全部字段
  @pagesize   int,         --每页记录数
  @page       int,         --指定页
  @condition  varchar(800),--查询条件
  @pages      int OUTPUT   --总页数
AS
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
         查询可以指定页大小、指定查询任意页、指定输出字段列表，返回总页数
作    者：pbsql
版    本：1.10
最后修改：2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
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='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
         ') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
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'
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
EXEC(@sql)
GO本存储过程高效，曾用500万条数据测试（已建索引），只返回分页只需3秒，影响效率的地方是计算总页数，若不需要可以注释掉--测试示例
declare @pages int
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,'*',10,2,'',@pages output--按id顺序取第二页
exec sp_page '#t','id',0,1,'*',10,2,'',@pages output--按id倒序取第二页
exec sp_page '#t','xx',1,0,'*',10,3,'',@pages output--按xx顺序取第三页
exec sp_page '#t','xx',1,1,'*',10,3,'',@pages output--按xx倒序取第三页
exec sp_page '#t','dt',2,0,'*',10,2,'',@pages output--按dt顺序取第二页
exec sp_page '#t','dt',2,1,'*',10,2,'',@pages output--按dt倒序取第二页select 总页数=@pagesdrop table #t

Answer 2

上面的写的太复杂了
我是这样写的，不过有点小问题：
CREATE PROCEDURE sp_selSjour @count int,@page int
as 
exec ('select top '+ @count +' * from db_sjournal where sjourID not in
(select top '+ @count*@page +' sjourID from db_sjournal order by sjourID) order by stime desc')
GO在@count*@page 处不知是什么问题

Answer 3

使用cast(@count*@page)竟然报cast出错？？？？
晕了

Answer 4

--version 2.0--转帖 风云版主的通用分页过程
--改了一下，加了个查询条件@condition：
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), --当前页最大号
  @condition  varchar(800) --查询条件
AS
DECLARE @sql varchar(8000)
IF @coltype=1
BEGIN
  SET @minid=''''+@minid+''''
  SET @maxid=''''+@maxid+''''
END
SET @sql=
  CASE @selecttype
    WHEN 1--前页
    THEN 'SELECT * FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
         ' '+@collist+' FROM '+@tb+' WHERE ('+@condition+') AND '+
         @col+'<'+@minid+' ORDER BY '+@col+' DESC) t ORDER BY '+@col
    WHEN 2--后页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+' WHERE ('+@condition+') AND '+@col+'>'+@maxid+
         ' ORDER BY '+@col
    WHEN 3--首页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+' WHERE ('+@condition+') ORDER BY '+@col
    WHEN 4--末页
    THEN 'SELECT * FROM (SELECT TOP '+CAST(@pagesize AS varchar)+' '+
         @collist+' FROM '+@tb+' WHERE ('+@condition+')'+
         ' ORDER BY '+@col+' DESC) t ORDER BY '+@col
    WHEN 5--指定页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+' WHERE ('+@condition+') AND '+@col+' NOT IN'+
         '(SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+
         ' FROM '+@tb+' WHERE ('+@condition+') ORDER BY '+@col+')'+
         ' ORDER BY '+@col
  END
EXEC(@sql)
GO
--测试
create table a(typeid int,name varchar(10))
insert a
select 101, 'a' union all
select 102, 'b' union all
select 103, 'c' union all
select 104, 'e' union all
select 105, 'a'
exec sp_page 'a','typeid',0,'*',5,2,2,'101','102','name<>''c'''
drop table a
drop procedure sp_page