1.5 SQL Server 2000 Stored Procedure ManagementThe most important consideration for using databases is the system speed and efficiency. Most of the existing DBMS systems provide methods or mechanisms to improve the system performance. SQL Server provides a method to centralize some fixed operations by the SQL Server database server to implement a task. This method is a stored procedure. The following describes how to create, modify, and delete a stored procedure.
1.5.1 create a stored procedureWhen creating a stored procedure, consider the following factors. ● The create procedure statement and other SQL statements cannot be combined into a single batch. ● The stored procedure creation permission belongs to the database owner by default. The owner can grant this permission to other users. ● A stored procedure is a database object and its name must comply with the identifier rules. ● You can only create stored procedures in the current database. In the Enterprise Manager, expand the database of the specified instance, select the "Stored Procedure" directory, right-click, and select the "Create stored procedure" command from the shortcut menu, open the Stored Procedure Properties dialog box, as shown in Figure 4-20. Figure 4-20 Stored Procedure attributes in the "text" text box, write the stored procedure. Consider creating a stored procedure. ● All input parameters and output parameters returned to the call process or batch processing. ● Execute programming statements for database operations (including calling other processes. ● Return the status value of the call process or batch processing to indicate success or failure (and cause of failure. After entering the syntax, click "Check Syntax" to check whether the syntax is correct. A message is displayed. If the prompt is correct, click OK to complete the creation of the stored procedure. The syntax for using CREATE procedure to create a stored procedure is as follows: Create procedure
Procedure_name[; Number] [{@ Parameter
Data_type} [Varying] [=
Default] [Output] [,... n] with {recompile | encryption | recompile, encryption}] [for replication]
SQL _statement[... N] the parameters are described as follows. ● Procedure_name: Specifies the name of the stored procedure to be created. ● Number: this parameter is an optional integer used to group stored procedures with the same name, so that the same group of processes can be removed with a drop procedure statement. ● @ Parameter: parameters in the process. One or more parameters can be declared in the create procedure statement. ● Data_type: Specifies the Data Type of a parameter. ● Varying: used to specify the result set supported by the output parameter. ● Default: Specifies the default value of a parameter. ● Output: indicates that this parameter is a return parameter. ● Recompile: indicates that SQL server does not save the execution plan of the stored procedure. ● Encryption: indicates that SQL Server encrypts the syscomments table. The text field of this table is the Stored Procedure text that contains the create procedure statement. ● For replication: used to specify that the stored procedure created for replication cannot be executed on the subscription server. ● As: Specifies the operations to be performed in the stored procedure. ● SQL _statement: Any number and types of transact-SQL statements to be included in the stored procedure. For example, create a stored procedure sp_showstudents to list the names and Department of all students in the studentsinfo table. The syntax is as follows: Create procedure sp_showstudentsas select sname, sdpt from studentsinfogo
1.5.2 modify stored procedureThe stored procedure can be changed based on user requirements or the definition of the base table. You can modify a stored procedure in the Stored Procedure Attributes dialog box. You can also use the alter procedure statement to change the stored procedure that was previously created by executing the create procedure statement, without changing the permissions or affecting the stored procedure or trigger. The syntax is as follows: Alter procedure
Procedure_name[; Number] [{@ Parameter
Data_type} [Varying] [=
Default] [Output] [,... n] [With {recompile | encryption | recompile, encryption}] [forreplication]
SQL _statement[... N] The meanings of parameters are basically the same as those used when creating a stored procedure.
1.5.3 delete a stored procedureYou can delete a stored procedure in Enterprise Manager: Right-click the stored procedure to be deleted and select the DELETE command from the shortcut menu. You can also use the drop command to delete stored procedures. The drop command can delete one or more stored procedures or stored procedure groups from the current database. The syntax is as follows: Drop procedure {
Procedure_name}[,... N] where,
Procedure_nameSpecifies the name of the stored procedure to be deleted. In addition, you can use this command to delete multiple stored procedures at the same time. You only need to use commas to separate the names of stored procedures to be deleted. For example, to delete the Stored Procedure sp_showstudents in the studentsinfo table, the syntax is as follows: Drop procedure sp_showstudents
