SQL Server database Stored Procedures

1. Create a stored procedure

Create proc

Stored Procedure name

[Parameter list (multiple parameters are separated by commas (,)]

As SQL statement

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 stored procedure name @ 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. As long as the aboveCodeRun the query analyzer once. 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 ).

 

Ii. Call of Stored Procedures

Previously, we have created a stored procedure named "upgetusername". The function of this stored procedure is to obtain the name of a user. After the storage process is established, the next step isProgramIn ASP.

Dim adocomm '// create an object for calling the Stored Procedure

Set adocomm = Createobject ("ADODB. Command") with adocomm'

// Set the connection and adoconn to the connected ADODB. connection object

. Activeconnection = adoconn'

// The type is stored procedure, and admo-storedproc = 4

. Commandtype = 4'

// Name of the stored procedure

. Commandtext = "upgetusername"

'// Set the user ID

. Parameters. Item ("@ intuserid"). value = 1

'// Execute the Stored Procedure

. Execute

'// Get the name of the user returned from the Stored Procedure

Response. Write "username:" &. Parameters. Item ("@ ostrusername"). Value

End

'// Release the object

Set adocomm = nothing

 

Iii. Practical Application of Stored Procedures

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
The stored procedure for user login has been set up. Now let's try it in the program. NOTE: If multiple statements exist in a region, you must use the begin... end keyword.

Quote:
// Create an object for calling the Stored Procedure
Set adocomm = Createobject ("ADODB. Command ")
With adocomm
'// Set the connection and set adoconn to the connected ADODB. connection object
. Activeconnection = adoconn'
// The type is stored procedure, and admo-storedproc = 4
. Commandtype = 4
'// Stored procedure name
. Commandtext = "upuserlogin"
'// Set the logon name
. Parameters. Item ("@ strloginname"). value = "admin"
'// Set the logon Password
. Parameters. Item ("@ strloginpwd"). value = "123456"
'// Execute the Stored Procedure
. Execute
'// Determine whether the logon is successful
If. Parameters. Item ("@ blnreturn"). value = 1 then
Response. Write "Congratulations! login successful! "
Else
Response. Write "no, it seems wrong... "
End if
End
'// Release the object
Set adocomm = nothing

Through the above steps, the simple user login verification process is also completed. Now, you only need to integrate it into the program to implement simple user login verification, you can handle other details by yourself.

The two stored procedures described above return only one value. Let's look at a stored procedure that returns a record set.

Quote:
Create proc upgetuserinfos
@ Intusergroup int
As
Begin
-- Extract qualified data from the database
Select uname, ugroup, ulastlogin from uuser where ugroup = @ intusergroup
-- Insert a column total
Union
Select 'Total number of students: ', count (ugroup), null from uuser where ugroup = @ intusergroup
End

Now let's take a look at ASP program calls.
Quote:
Dim adocomm
Dim adort
'// Create an object for calling the Stored Procedure
Set adocomm = Createobject ("ADODB. Command ")
Set adors = Createobject ("ADODB. recordset ")
With adocomm
'// Set the connection and set adoconn to the connected ADODB. connection object
. Activeconnection = adoconn
'// The type is stored in the process, admo-storedproc = 4
. Commandtype = 4
'// Stored procedure name
. Commandtext = "upgetuserinfos"
'// Set the user group
. Parameters. Item ("@ intusergroup"). value = 1
'// Execute the stored procedure. Unlike the preceding examples, the recordset open method is used here.
Adors. Open adocomm
'// Display the first value
Response. Write adors. Fields (0). Value
End
'// Release the object
Set adors = nothing
Set adocomm = nothing