Getting Started with SQL Server 7.0 (5)

Develop applications using SQL Server
Write stored procedures and triggers
Stored procedures and triggers are a set of Transact SQL query statements created by users and resident on the server. Triggers are executed by the system under specific conditions. Stored procedures can improve application performance. When a client program needs to access data, it generally has to go through 5 steps to access the data:
1) The query statement is sent to the server.
2) The server compiles SQL code.
3) Optimize the execution plan of the generated query.
4) The data engine executes the query.
5) The result is sent back to the client program.
The stored procedure is compiled when it is created. When the stored procedure is executed for the first time, SQL Server generates the query execution plan and stores it for future use. When making a request through a stored procedure, steps 2 and 3 above are no longer required, which can greatly improve system performance. Improves performance even on step 1. Because the statement sent to the server at this time is only an EXECUTE statement of a stored procedure, not a large and complex query. This feature reduces network traffic.
In addition to performance improvements, stored procedures also provide the convenience of centralized maintenance of applications. If you embed the query into your application. If changes need to be made to the query, the application needs to be recompiled and republished to all clients. In a stored procedure, modifications are transparent to the user; it only requires recompiling the stored procedure on the server.
Stored procedures can also provide a security mechanism. Although the user may not have the authority to execute the commands in the stored procedure, it may have the authority to execute the stored procedure itself. Sometimes, system administrators do not give users data modification (UPDATE, INSERT, and DELETE) rights. The stored procedures created can perform these operations. Of course the user needs to have the authority to execute the stored procedure.

Create stored procedures
Stored procedures can achieve the following purposes:
· With parameters.
· Return status value.
· Call other stored procedures.
· Executed on the remote server.
The stored procedure has an entry in the "sysobjects" system table, its type is "P". The text of the stored procedure is stored in the "syscomments" system table. To create a stored procedure, use the Transact SQL command CREATE PROCEDURE.
For example:
USE pubs
GO

CREATE PROCEDURE ap_GetAuthorsForPublisher
AS
SELECT a.au_lname,a.au_fname
F ROM 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

CREATE The grammar of the procedure statement is as follows:
Create ProC [Dure] procedure_name [; number]
[@parameter_name] [Output] [, _n]]
[Recompile | Encryption}]
[for REPLICATION]
AS
Number is an integer used to group procedures with the same name. Grouping combines all procedures into one group through the drop procedure statement.
@parameter_name specifies the name of the parameter.
RECOMPILE means that the process must be compiled every time it is executed.
ENCRYPTION indicates that the text of the process should be encrypted in the "syscomments" table.
FOR REPLICATION indicates that the process cannot be executed on the submitting server.

Pass parameters to stored procedures
Stored procedures can accept parameters.
Note: The parameters of the procedure can also be user-defined data types.

Give the parameter a default value
Users can also define default values ​​for parameters in stored procedures. When required parameters are not provided at execution time, the system uses default values ​​as parameters. If neither a default value is defined nor parameters are provided at execution time, SQL Server returns an error. It's a good practice to define default values ​​in stored procedures and use some logic to detect whether parameters are specified and take appropriate action.
For example:
USE pubs
GO

CREATE PROCEDURE ap_GetAuthorsForPublisher
@PublisherName varchar(40) = 'New Moon Books'
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 = @PublisherName
GO

The above is the content of Getting Started with SQL Server 7.0 (V). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!