SQL存储过程添加新记录

SQL存储过程添加新记录

SQL数据库存储过程语句

CREATE PROC sp_AddUser
-- 存储过程参数
 @username nvarchar(20)  ,
 @password nvarchar(20) ,
 @realname nvarchar(30) ,
 @sex nvarchar(10) ,
 @useremail nvarchar(60) ,
 @regtime smalldatetime,
 @regip nvarchar(32) ,
 @regfrom nvarchar(250) ,
 
 -- 输出参数，注意：输出参数后面都要带OUTPUT
 @RegSucceed int OUTPUT
AS
--检测用户名唯一性
--检测电子邮件唯一性
 -- 如果Select为空
 IF Exists(Select * from  UserCurrency Where username = @username)
  BEGIN
   -- 给@Succeed赋值'0'
   Set @RegSucceed  =-1
  END

 ELSE-- 否则
  
 IF Exists(Select * from  UserCurrency Where useremail = @useremail)
  BEGIN
   Set @RegSucceed  =-2
  END
 ELSE   BEGIN
   Set @RegSucceed = 1
   -- 往数据库Uname中添加数据
   INSERT INTO UserCurrency (username,password,realname,sex,useremail,regtime,regip,regfrom)
    VALUES (@username,@password,@realname,@sex,@useremailty,@address,@postcode,@regtime,@regip,@regfrom)
  END

-- 执行
GO

ASP调用存储过程语句

connstr = "PROVIDER=SQLOLEDB.1;Data Source='本机电脑名';Initial Catalog='数据库名';Persist Security Info=True;User ID='登陆用户';Password='登陆密码';Connect Timeout=30"

Set conn=Server.CreateObject("Adodb.Connection")
conn.Open connstr

Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = conn 'conn是数据库连接字串
MyComm.CommandText = "sp_AddUser" '指定存储过程名
MyComm.CommandType = 4 '表明这是一个存储过程
MyComm.Prepared = true '要求将SQL命令先行编译

'测试给存储过程传入参数赋值
MyComm.Parameters.append MyComm.CreateParameter("@username",200,1,20,username)
MyComm.Parameters.append MyComm.CreateParameter("@password",200,1,20,md5(password))
MyComm.Parameters.append MyComm.CreateParameter("@realname",200,1,30,realname)
MyComm.Parameters.append MyComm.CreateParameter("@sex",200,1,10,sex)
MyComm.Parameters.append MyComm.CreateParameter("@useremail",200,1,60,useremail)
MyComm.Parameters.append MyComm.CreateParameter("@regtime",135,1,30,regtime)
MyComm.Parameters.append MyComm.CreateParameter("@regip",200,1,32,regip)
MyComm.Parameters.append MyComm.CreateParameter("@regfrom",200,1,250,regfrom)

MyComm.Parameters.append MyComm.CreateParameter("@RegSucceed",3,2,4)

Set rs = MyComm.Execute()
RegStatus=MyComm.Parameters("@RegSucceed").Value
Select Case RegStatus

Case -1:
rs.close
set rs=nothing
set MyComm=nothing
conn.close
set conn=nothing
response.write "<script language='JavaScript'>alert('此用户已经被注册了！请重新填写一个用户名!');history.go(-1);</script>"
response.end
Case -2:
rs.close
set rs=nothing
set MyComm=nothing
conn.close
set conn=nothing
response.write "<script language='JavaScript'>alert('此电子邮件已经被注册了！请重新填写电子邮件!');history.go(-1);</script>"
response.end
Case Else:
rs.close
set rs=nothing
set MyComm=nothing
conn.close
set conn=nothing
End Select