When we write a user authentication program, it is easy to use ASP to call SQL statements to retrieve whether there are records that match the conditions in the data table, and then use ASP to perform related processing.
All roads lead to Rome! Of course, we can also use the stored procedure of the SQL SERVER database to easily implement this function. Although it is relatively complex, its efficiency improvement is obvious, because the stored procedure is a program that has been compiled in the database, and we only need to use ASP to correctly pass the various parameters used by it.
This article also mainly wants to introduce to you how to call a stored procedure with parameters in ASP through a simple example. I hope you can get more inspiration from it.
The first step is to create the data table userinfo
id int(4) not null,
fullname varchar(50) not null,
password varchar(20) not null,
nikename varchar(50) not null
The second step is to create the stored procedure usercheck
CREATE procedure usercheck
@infullname varchar(50),
@inpassword varchar(50),
@outcheck char(3) OUTPUT
as
if exists(select * from userinfo where fullname=@infullname and password=@inpassword )
select @outcheck='yes'
else
select @outcheck='no'
Note: A stored procedure with three parameters is created here. The first parameter @infullname is an input parameter (user name); the second parameter @inpassword is also an input parameter. (password); the third parameter @outcheck, this is an output parameter, (whether this user exists), when defining the output parameter, the word "OUTPUT" must be added after the data type.
Then, we take the first two input parameters to retrieve whether there is a qualified user in the SQL statement. If there is, the value of the output parameter is "yes", otherwise it is "no".
The third step is to write an ASP program and call the stored procedure
<%
'Form submission flag
if request("ok")=1 then
'Establish database connection
Set Comm=Server.CreateObject("ADODB.Command")
Comm.ActiveConnection="DSN=localserver;UID=sa;PWD=;Database=chaiwei"
'Establish a stored procedure connection with the comm object, 4 means the connection type is a stored procedure
Comm.CommandText="usercheck"
Comm.CommandType=4
'Create the parameter method of the comm object with p1 as the name. Append the first parameter fullname to the p1 collection 'fullname' The name of the first parameter called '200 Parameter type varchar type '1 Parameter flow to the input, the input is 1, the output is 2
'50 parameter length 50
'request("fullname") Assign parameter initial value
Set p1=Comm.CreateParameter("fullname",200,1,50,request("fullname"))
Comm.Parameters.Append p1
'Create the parameter method of the comm object with p1 as the name. Append the second parameter password to the p1 collection
'The specifics are the same as above
Set p1=Comm.CreateParameter("password",200,1,20,request("password"))
Comm.Parameters.Append p1
'Create the parameter method of the comm object with p1 as the name. Append the third parameter check to the p1 collection
'129 Parameter type char type
'2 Parameter flow to output
'3 Parameter length 3
Set p1=Comm.CreateParameter("check",129,2,3)
Comm.Parameters.Append p1
'Run the stored procedure
Comm.Execute
'Propose the result and process it
if comm("check")="yes" then
response.write "Welcome to the system! Username:" & comm("fullname") & "Password:" & comm("password")
else
response.write "Sorry, you have not registered yet!"
end if
'Release connection
Set Comm=nothing
else
'form part
%>