SQL Server 7.0 (5)

Use SQL Server to develop applications
Writing stored procedures and triggers
Stored procedures and triggers are a set of Transact SQL query statements created by users and residing on the server. A trigger is executed by the system under certain conditions. Stored procedures can improve application performance. When the customer program needs to access data, it generally takes five steps to access the data:
1) the query statement is sent to the server.
2) compile the SQL code on the server.
3) optimize the execution plan for query generation.
4) The data engine executes the query.
5) The result is returned to the client program.
The stored procedure is compiled during creation. When the stored procedure is executed for the first time, SQL Server generates a query execution plan and stores it for future use. When a request is sent through the stored procedure, steps 2nd and 3rd are missing, which can greatly improve the system performance. Performance can be improved even in step 3. The statement sent to the server is only an execution statement of the stored procedure, rather than a large and complex query. This feature can reduce network traffic.
In addition to performance improvement, stored procedures also provide the ability to conveniently centrally maintain applications. If the query is embedded in the application. If the query needs to be changed, the application needs to be re-compiled and re-released to all clients. In the stored procedure, modification is transparent to users, and it only needs to re-compile the stored procedure on the server.
Stored procedures also provide security mechanisms. Although users may not have the right to execute commands in stored procedures, they may have the right to execute stored procedures themselves. Sometimes, the system administrator does not give users the power to modify data (UPDATE, INSERT, and DELETE. The created stored procedure can perform these operations. Of course, you must have the right to execute the stored procedure.
Create a stored procedure
Stored procedures can achieve the following goals:
· With parameters.
· Return status value.
· Call other stored procedures.
· Run the command on a remote server.
A stored procedure has a table item in the "sysobjects" system Table. Its type is "P ". The stored procedure text is stored in the syscomments system table. To CREATE a stored PROCEDURE, run the create procedure command.
For example:
USE pubs
GO

Create procedure ap_GetAuthorsForPublisher
AS
SELECT a. au_lname, a. au_fname
FROM authors a, titleauthor ta, titles t, publishers p
WHERE a. au_id = ta. Au_id
AND ta. Title_id = t. title_id
AND t. pub_id = p. pub_id
AND p. pub_name = 'New Moon Books'
GO