ASP Stored Procedure (SQL ASP calls Stored Procedure)

Stored Procedure (SQL sample)
Today, I am sending an SQL stored procedure for you to understand. CopyCode The Code is as follows: Create procedure login_verify

(
@ Community_id int, -- get the value
@ Username varchar (20 ),
@ Password varchar (40 ),
@ Result tinyint output
)

As

Set nocount on

Declare @ service_deadline_date smalldatetime, @ community_setting_max_online_count int --- define a variable as a short Date Format

Select @ community_setting_max_online_count = community_setting_max_online_count, @ service_deadline_date = service_deadline_date from community_info where community_id = @ community_id -- here is the maximum number of logins

If datediff (D, @ service_deadline_date, getdate ()> 10 -- in fact, this is to limit the user's validity period, and calculate the current date and the record date in the database, for example, the time is greater than 10 days, returns @ result = 11
Begin
Set @ result = 11 -- expired
Return
End

If (select count (*) from online_user where = @ community_setting_max_online_count "> community_id = @ community_id) >=@ community_setting_max_online_count -- compare the number of current users based on the record settings
Begin
Set @ result = 10 -- the number of online users exceeds the limit -- Return @ result = 10
Return
End

Declare @ stamia int, @ last_update_stamia_date smalldatetime, @ level_id int -- defines the short-date Integer type of the variable.
Declare @ userid int, @ user_role int
Select @ userid = userid, @ user_role = user_role, @ stamia = stamia, @ last_update_stamia_date = last_update_stamia_date, @ level_id = level_id from user_info where username = @ username and password = @ password and community_id = @ community_id and user_type = 0

-- Write some information from the user information table to the three Defined variables

If @ userid is not null ---- if @ userid remains null
Begin -- the user name and password are verified successfully.
Set @ result = 1 -- check successful
Return
End
Else
Begin
Set @ result = 0 --- Logon Failed
End

Set nocount off

Go

Let's name the above process login_verify

Written as the place where security authentication is called in ASP code

''' Defined Conn in advance

Set cmd. activeconnection = Conn
Cmd. commandtext = "login_verify"
Cmd. commandtype = & h0004

@ Community_id int, -- get the value
@ Username varchar (20 ),
@ Password varchar (40 ),
@ Result int

Cmd. Parameters. append cmd. createparameter ("@ community_id", 3)
Cmd. Parameters. append cmd. createparameter ("@ username", 200)
Cmd. Parameters. append cmd. createparameter ("@ password", 200)

CMD ("@ community_id") = SESSION ("community_id ")
CMD ("@ username") = request ("userid ")
CMD ("@ password") = request ("userid ")

Cmd.exe cute

Dim result

Result = cmd ("@ result ")

Conn. Close

If trim (result) = "1" then

''' Logon success tips and operations

Else

''' Logon Failure prompts and operations

End if