SQL Server database development-stored procedure application
SQL SERVER database development-stored procedure application
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.
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:
Example:
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.
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.
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.
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.
Now let's take a look at ASP program calls.
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.
