drop procedure propageset
go
Create Procedure ProPageSet
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页(必须是唯一性的字符，比如标识种子)
@colorder varchar(50), --需要排序的字段(为空，则默认为col)
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
--@sqlout nvarchar(4000),---返回sql语句
AS
SET NOCOUNT ON
Declare @intResult Int
Begin Tran
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @colorder is null or rtrim(@colorder)=''
set @colorder = @col
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 @intResult=COUNT(*) FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数
Select @pages=CEILING((@intResult+0.0)/@pagesize)--计算总页数
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 '+@colorder
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 '+@colorder+' DESC'
IF @page=1--第一页
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@colorder+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--set @sqlout = @sql
EXEC(@sql)
--print 'Sql语句输出为: ' + @sqlout
-------------------------------------------------------------------------------------------------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @intResult
End
GO
调用:
示例:先将该存储过程在pubs中建立。
asp调用示例:对employee表进行分页
表结构
Create TABLE [dbo].[employee] (
[emp_id] [empid] NOT NULL ,
[fname] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[minit] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[lname] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[job_id] [smallint] NOT NULL ,
[job_lvl] [tinyint] NULL ,
[pub_id] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hire_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
代码文件:
Set cmd = Server.CreateObject("ADODB.Command")
with cmd
.ActiveConnection = conn '数据库连接字串
.CommandText = "ProPageSet" '指定存储过程名
.CommandType = 4 '表明这是一个存储过程
.Prepared = true '要求将SQL命令先行编译
.Parameters.Append .CreateParameter("RETURN",3,4,4) '返回值
.Parameters.append .CreateParameter("@tb",200,1,50,"t_admin") '分页时要查询的表名
.Parameters.append .CreateParameter("@col",200,1,50,"f_id") '按该列来进行分页
.Parameters.append .CreateParameter("@colorder",200,1,50,orderfield) '排序字段
.Parameters.append .CreateParameter("@orderby",11,1,1,orderway) '排序方式，0为顺序，1为倒序
.Parameters.append .CreateParameter("@collist",200,1,800,"*") '每页中要显示的字段，以逗号隔开
.Parameters.append .CreateParameter("@pagesize",3,1,4,mypagesize) '每页记录数
.Parameters.append .CreateParameter("@page",3,1,4,page) '指定页数
.Parameters.append .CreateParameter("@condition",200,1,800,sqlwhere) '查询条件where 中的条件语句
.Parameters.Append .CreateParameter("@pages",3,2,4) '总页数output
Set rs = .Execute
end with
while not rs.eof
'显示输出分页的数据库内容
rs.movenext
wend
rs.close '取返回值前一定要关闭，否则取不到
totalrecord = cmd(0) '总记录数
totalpage = cmd(9) '总页数
set rs = nothing
set cmd = nothing
生成的Sql语句其实就是这样的:(即每页5条记录，第三页)
Select TOP 5 * FROM t_admin Where f_id>
(Select max(f_id) FROM
(Select TOP 10 f_id FROM t_admin orDER BY f_id) t)
orDER BY f_username DESC