SQL Server database development-stored procedure application

1. Create a stored procedure

Like a data table, we need to create a stored procedure before using it. Its concise syntax is:

 

Create proc stored procedure name
[Parameter list (multiple parameters are separated by commas (,)]
As
SQL statement

For example

Create proc upgetusername
@ Intuserid int,
@ Ostrusername nvarchar (20) Output -- parameters to be output
As
Begin
-- Assign the uname value to the @ ostrusername variable, that is, the parameter to be output.
Select @ ostrusername = uname from uuser where uid = @ intuserid
End

The create proc statement (the complete statement is create procedure) tells SQL server that a stored procedure needs to be created now. upgetusername is the name of the stored procedure, @ intuserid and @ ostrusername are the two parameters of the stored procedure respectively. Note that in SQL Server, all User-Defined variables start, the output keyword indicates that this parameter is used for output. As is followed by the Stored Procedure content. If you run the preceding code once in the "query analyzer", SQL Server creates a stored procedure named "upgetusername" in the current database. You can open "Enterprise Manager", select the database for the current operation, and select "Stored Procedure" in the tree list on the left ", now you can see the stored procedure you just created in the list on the right (if not, refresh it ).

Iii. Practical Application of Stored Procedures

User Login is often used in ASP projects. I believe many of my friends have done similar systems, but there may not be many friends who use stored procedures for verification. We will use it as an example, write a simple Stored Procedure for user login verification.

Create proc upuserlogin
@ Strloginname nvarchar (20 ),
@ Strloginpwd nvarchar (20 ),
@ Blnreturn bit output
As
-- Defines a variable temporarily used to save the password
Declare @ strpwd nvarchar (20)
Begin
-- Query the password of the current user from the table and assign the value to the @ strpwd variable. We will compare it below
Select @ strpwd = uloginpwd from uuser where uloginname = @ strloginname

If @ strloginpwd = @ strpwd
Begin
Set @ blnreturn = 1
-- Updates the last logon time of a user.
Update uuser set ulastlogin = getdate () Where uloginname = @ strloginname
End
Else
Set @ blnreturn = 0
End