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