存储过程

来源：互联网发布：手机淘宝的设置在哪编辑：程序博客网时间：2024/05/16 13:47

存储过程
CREATE PROCEDURE sp_newpage
@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),--查询条件
@RecordCount Int Output,
@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--计算总页数

SET @sql= 'SELECT @RecordCount=COUNT(*) FROM '+@tb+@where2
EXEC sp_executesql @sql,N '@RecordCount int OUTPUT ',@RecordCount 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
asp调用
 <head>
<meta http-equiv= "Content-Language " content= "zh-cn ">
</head>

<%
set conn=server.createobject( "adodb.connection ")
connstr= "server=127.0.0.1;uid=sa;pwd=数据库密码;database=数据库;PROVIDER=SQLOLEDB "
'conn.open connstr
%>
<%
dim records
page=request( "page ")
if not isnumeric(page) then
page=1
end if

if page <1 then
page=1
end if

set rs=server.createobject( "adodb.recordset ")

Set CmdSP = Server.CreateObject( "ADODB.Command ")

CmdSP.ActiveConnection = connstr 'MyConStr是数据库连接字串
CmdSP.CommandText = "sp_newpage " '指定存储过程名
CmdSP.CommandType = 4 '表明这是一个存储过程
CmdSP.Prepared = true '要求将SQL命令先行编译
'返回值
CmdSp.Parameters.Append CmdSp.CreateParameter( "RETURN_VALUE ",adInteger,adParamReturnValue,4)
'入参(表名)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@tb ",adVarChar,adParamInput,50)
'入参(按该列来进行分页)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@col ",adVarChar,adParamInput,50)
'入参(col列的类型,0-数字类型,1-字符类型,2-日期时间类型)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@coltype ",adInteger,adParamInput)
'入参(排序,0-顺序,1-倒序)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@orderby ",adInteger,adParamInput)
'入参(要查询出的字段列表,*表示全部字段)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@collist ",adVarChar,adParamInput,800)
'入参(每页记录数)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@pagesize ",adInteger,adParamInput)
'入参(指定页)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@page ",adInteger,adParamInput)
'入参(查询条件)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@condition ",adVarChar,adParamInput,800)
'出参(总记录数)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@RecordCount ",adInteger,adParamOutput)
'出参(总页数)
CmdSp.Parameters.Append CmdSp.CreateParameter( "@pages ",adInteger,adParamOutput)

CmdSp( "@tb ")= "你的表名 "
CmdSp( "@col ")= "表的id "
CmdSp( "@coltype ")=0
CmdSp( "@orderby ")=0
CmdSp( "@collist ")= "* "
CmdSp( "@pagesize ")=50
CmdSp( "@page ")=page
CmdSp( "@condition ")= " "
Set rs =CmdSp.Execute

if rs.state = 0 then '未取到数据，rs关闭
recordcount = -1
else

rs.close '只有关闭才能取出返回值
recordpage=CmdSp.parameters( "@pages ")
recordcount=CmdSp.parameters( "@recordcount ")
response.write "总记页数= "&CmdSp( "@pages ")
response.write " 总记录数= "&CmdSp.parameters( "@recordcount ")
rs.open '要取出记录集，则要再打开
if int(page)> int(recordpage) then
page=recordpage
end if

%>

<TABLE width= "100% " bordercolorlight= "#C0C0C0 " cellspacing= "1 " bordercolor= "#C0C0C0 " bordercolordark= "#FFFFFF " border= "1 ">
<TR>
<TD height= "20 " bgcolor= "#C0C0C0 ">
<p align= "center "> 测试1 </TD>

<TD bgcolor= "#C0C0C0 ">
<p align= "center "> 测试2 </TD>

<TD bgcolor= "#C0C0C0 ">
<p align= "center "> 测试3 </TD>
</TR>
<%if not rs.eof then
do while not rs.eof
%>
<TR>
<TD height= "20 "> <%if not isnull(rs( "*** ")) and rs( "*** ") <> " " then response.write rs( "*** ") else response.write "  "%> </TD>

<TD> <%if not isnull(rs( "*** ")) and rs( "*** ") <> " " then response.write rs( "*** ") else response.write "  "%> </TD>

<TD> <%if not isnull(rs( "*** ")) and rs( "*** ") <> " " then response.write rs( "*** ") else response.write "  "%> </TD>
</TR>
<%
rs.movenext
loop
%>
<%else%>

<%end if%>
<%rs.close:set rs=nothing%>

<TR>
<TD height= "20 " colspan= "3 ">
<p align= "center "> 总共有 <%=recordcount%> 条记录 [ <a href= "proctest.asp?page=1 "> 首页 </a> ]
[ <a href= "proctest.asp?page= <%if (page-1)> 0 then response.write (page-1) else response.write "1 "%> "> 上一页 </a> ] [ <a href= "proctest.asp?page= <%if int(page+1)> int(recordpage) then response.write recordpage else response.write (page+1)%> "> 下一页 </a> ] [ <a href= "proctest.asp?page= <%=recordpage%> "> 末页 </a> ] </TD>

</TR>

</TABLE>
<%end if%>