Some may have been using SQL SERVER for development for some time, but they have not or seldom used stored procedures in projects. Some may think that there is no need to use stored procedures. In fact, when a project is completed in the maintenance phase, you will find that the stored procedure has brought us benefits. It is easy to modify and cannot change our applications, you only need to modify the content of the stored procedure, and increase the speed of our program.
| Reference: |
Definitions in SQL SERVER books online:
Stored procedures are a set of statements that can be saved to accept and return user-supplied parameters.
You can create a process for permanent use, or temporary use in a session (Partial temporary process), or temporary use in all sessions (Global temporary process ).
You can also create a stored procedure that runs automatically when Microsoft SQL Server is started. |
|
To use stored procedures, first we must be familiar with some basic T-SQL statements, because stored procedures are made up of a set of T-SQL statements, and we need to understand some concepts about functions, procedures, because we need to call the stored procedure in the application, just as we call the function of the application, but the calling method is somewhat different.
The following describes how to establish and use a stored procedure.
1. Create a stored procedure
Like a data table, we need to create a stored procedure before using it. Its concise syntax is:
| Reference: |
Create proc stored procedure name
[Parameter list (multiple parameters are separated by commas (,)]
AS
SQL statement |
|
Example:
| Reference: |
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 ).
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 set up, the next step is to call it in the application. Next, let's take a look at the call in the ASP program.
| Reference: |
Dim adoComm
'// 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 in the process, admo-storedproc = 4
. CommandType = 4
'// Stored procedure name
. 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 |
|
Through the above two steps, we can create and use simple stored procedures. Next we will look at a slightly more complex stored procedure to further understand the application of the stored procedure.
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.
| Reference: |
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.
| Reference: |
Dim adoComm
'// 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 in the process, 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.
| Reference: |
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.
| Reference: |
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 |
|
How can this problem be solved? Is it easy? However, stored procedures are not only useful. They also have more powerful functions, such as using cursors and temporary tables to access multiple tables, you can even call data in multiple databases and then return the data to the user, which can be explored slowly during use.
Well, the stored procedure application will be introduced here first. If you have any questions in this article, please follow up and I will try my best to answer them. Next time, I will introduce the trigger application.
