ASP调用存在过程
网上找了相关ASP调用存储过程的例子,但要不尽人意。完了最后,不知如何下手, 以下就自己写的存储过程作出解释。
1存储过程代码:
2--功能描述:进行登录验证
3--需求分析:要求输入用户名或Email
4--输入:用户名,邮箱名(可选),密码
5--输出:同时返回记录集
6
7IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'PCheckLogin')
8DROP PROC PCheckLogin
9GO
10CREATE PROC PCheckLogin
11 @UserName VARCHAR(30),@Password VARCHAR(30)
12AS
13BEGIN
14 SET NOCOUNT ON
15 SELECT UserId,UserLevelId,UserName,NickName,FullName,UnitId,DepId,LastLogin,Email
16 FROM UserAdmin where (UserName = @UserName or Email= @UserName) and [Password] = @Password
17 IF @@ROWCOUNT = 0
18 RETURN 0
19 ELSE
20 RETURN 1
21END
22--调用示例
23--DECLARE @bStatic bit
24--EXEC @bStatic = PCheckLogin 'xgy','2098f7461360b0f2'
25--PRINT @bStatic
26
2--功能描述:进行登录验证
3--需求分析:要求输入用户名或Email
4--输入:用户名,邮箱名(可选),密码
5--输出:同时返回记录集
6
7IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'PCheckLogin')
8DROP PROC PCheckLogin
9GO
10CREATE PROC PCheckLogin
11 @UserName VARCHAR(30),@Password VARCHAR(30)
12AS
13BEGIN
14 SET NOCOUNT ON
15 SELECT UserId,UserLevelId,UserName,NickName,FullName,UnitId,DepId,LastLogin,Email
16 FROM UserAdmin where (UserName = @UserName or Email= @UserName) and [Password] = @Password
17 IF @@ROWCOUNT = 0
18 RETURN 0
19 ELSE
20 RETURN 1
21END
22--调用示例
23--DECLARE @bStatic bit
24--EXEC @bStatic = PCheckLogin 'xgy','2098f7461360b0f2'
25--PRINT @bStatic
26
ASP调用过程
<% dim sUserName,sPwd,sql,Comm,rs
sUserName=request.Form("username")
sPwd=md5(trim(request.Form("Password")))
'调用注册验证存储过程
Set Comm = Server.CreateObject("ADODB.Command")
with Comm
.ActiveConnection = Conn 'MyConStr是数据库连接字串
.CommandText = "PCheckLogin" '指定存储过程名
.CommandType = 4 '表明这是一个存储过程
.Prepared = true '要求将SQL命令先行编译
'参数说明:参数名、参数数据类型、参数类型、数据长度、参数值
.Parameters.Append .CreateParameter("RETURN",2,4)
.Parameters.Append .CreateParameter("@UserName",200,1,30,sUserName)
.Parameters.append .CreateParameter("@iPage",200,1,30,sPwd)
Set rs = .Execute
end with
rs.close '想使用comm(0)的值必须先关闭记录集,用后打开,否则为0
if comm(0) = 0 then '验证失败
response.Write("<script language=javascript>alert('用户名或密码错误,请重试!');history.back();</script>")
response.end()
else
rs.open
iUserId=rs("UserId")
response.Cookies("UserId")=iUserId
response.Cookies("UserLevel")=rs("UserLevelId")
response.Cookies("UserName")=rs("UserName")
response.Cookies("NickName")=rs("NickName")
response.Cookies("FullName")=rs("FullName")
response.Cookies("UnitId")=rs("UnitId")
response.Cookies("DepId")=rs("DepId")
response.Cookies("LastLogin")=rs("LastLogin")
response.Cookies("email")=rs("Email")
'存储过程:EXEC PUpdatePoint 点数,登录次数,发表文章数(可选参数),用户ID
sql="EXEC PUpdatePoint 1,1,0,"&iUserId
conn.execute(sql)
response.redirect("../Manage/manage.asp")
end if
rs.close
set Comm = nothing
set rs=nothing %>
sUserName=request.Form("username")
sPwd=md5(trim(request.Form("Password")))
'调用注册验证存储过程
Set Comm = Server.CreateObject("ADODB.Command")
with Comm
.ActiveConnection = Conn 'MyConStr是数据库连接字串
.CommandText = "PCheckLogin" '指定存储过程名
.CommandType = 4 '表明这是一个存储过程
.Prepared = true '要求将SQL命令先行编译
'参数说明:参数名、参数数据类型、参数类型、数据长度、参数值
.Parameters.Append .CreateParameter("RETURN",2,4)
.Parameters.Append .CreateParameter("@UserName",200,1,30,sUserName)
.Parameters.append .CreateParameter("@iPage",200,1,30,sPwd)
Set rs = .Execute
end with
rs.close '想使用comm(0)的值必须先关闭记录集,用后打开,否则为0
if comm(0) = 0 then '验证失败
response.Write("<script language=javascript>alert('用户名或密码错误,请重试!');history.back();</script>")
response.end()
else
rs.open
iUserId=rs("UserId")
response.Cookies("UserId")=iUserId
response.Cookies("UserLevel")=rs("UserLevelId")
response.Cookies("UserName")=rs("UserName")
response.Cookies("NickName")=rs("NickName")
response.Cookies("FullName")=rs("FullName")
response.Cookies("UnitId")=rs("UnitId")
response.Cookies("DepId")=rs("DepId")
response.Cookies("LastLogin")=rs("LastLogin")
response.Cookies("email")=rs("Email")
'存储过程:EXEC PUpdatePoint 点数,登录次数,发表文章数(可选参数),用户ID
sql="EXEC PUpdatePoint 1,1,0,"&iUserId
conn.execute(sql)
response.redirect("../Manage/manage.asp")
end if
rs.close
set Comm = nothing
set rs=nothing %>
就是这样的。但不知道存储过程还可以优化嘛!请大家多多指教!