MS SQL Basics Tutorial: Overview of Stored procedures

In large database systems, stored procedures and triggers play an important role. Both a stored procedure and a trigger are collections of SQL statements and Process Control statements. On

In essence, triggers are also a stored procedure. A stored procedure generates execution at operation time, so it executes quickly when it is later run. SQL Server 2000 not only provides the functionality of user-defined stored procedures, but also provides many system stored procedures that can be used as tools.

The concept of 12.1.1 stored procedures

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to complete a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter, if the stored procedure has parameters.

There are two types of stored procedures in the series version of SQL Server: System-supplied stored procedures and user-defined stored procedures. System procedures are primarily stored in the master database and prefixed with sp_, and system stored procedures provide support for system administrators in managing SQL Server by obtaining information primarily from system tables. Through system stored procedures, many of the administrative or informational activities in MS SQL Server, such as understanding database objects, database information, can be successfully completed. Although these system stored procedures are placed in the master database, they can still be invoked in other databases without having to precede the stored procedure name with the database name. And when you create a new database, some system stored procedures are created automatically in the new database. A user-defined stored procedure is a stored procedure that is created by a user and capable of accomplishing a particular function, such as querying the data information required by a user. The stored procedures involved in this chapter refer primarily to user-defined stored procedures.

Advantages of 12.1.2 Stored procedures

When using MS SQL Server to create an application, Transaction-sql is a major programming language. If you use Transaction-sql for programming, there are two ways to do it. One is to store the Transaction-sql program locally and create an application to send commands to SQL Server to process the results. The second is that some of the programs written in Transaction-sql can be stored in SQL Server as stored procedures, and an application is created to invoke the stored procedure, and the data processing stored procedure can return the result set to the caller by receiving parameters, and the format of the result set is determined by the caller ; Returns the status value to the caller, indicating whether the call was successful or failed, including an action statement against the database, and can invoke another stored procedure in one stored procedure.

We typically prefer the second approach, where you use stored procedures in SQL Server instead of invoking a program written by Transaction-sql on a client computer because stored procedures have the following advantages:

(1) Stored procedures allow standard component programming

A stored procedure can be called multiple times in a program after it has been created without having to rewrite the SQL statement for that stored procedure. and database professionals can modify stored procedures at any time, but have no effect on the application source code (because the application source code contains only the call statements of stored procedures), thereby greatly improving the portability of the program.

(2) Stored procedures can achieve faster execution speed

If an action contains a large amount of transaction-sql code or is executed several times, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, the query optimizer analyzes, optimizes, and gives an execution plan in the system table that is ultimately present when a stored procedure is first run. The Transaction-sql statements for batches are compiled and optimized every time they run, so the speed is relatively slow.

(3) Stored procedures can reduce network traffic

For the same operation against a data database object, such as query, modification), if the Transaction-sql statement involved in this operation is organized into a stored procedure, then when the stored procedure is invoked on the client computer, only the calling statement is transmitted in the network, otherwise it will be more than one SQL statement. Thus greatly increasing the network traffic, reducing Network load.

(4) Stored procedures can be used as a security mechanism to fully utilize

The system administrator limits the permission to execute a stored procedure, which can limit the access to the data, avoid unauthorized users ' access to the data, and ensure the security of the data. (We will make a clearer introduction to this application of the stored procedures in Chapter 14, "User and security management for SQL Server")

Note: The stored procedure is not the same as the function, although it has both parameters and return values. The return value of the stored procedure simply indicates whether the execution was successful, and it cannot be called directly as a function, that is, when the stored procedure is invoked, there must be an EXEC reserved word in front of the stored procedure name (how to execute the stored procedure, see the next word in this chapter).

See the full suite of MS SQL Basics Tutorials