In ms SQL Server 2000, there are two ways to create a stored procedure: one is to use the create procedure command using the transaction-SQL command, and the other is to use the graphical management tool Enterprise Manager. Creating a stored procedure using transaction-SQL is a fast method, but it is easier for beginners to understand and use enterprise manager.
When creating a stored procedure, you must determine the three components of the stored procedure;
- All input parameters and output parameters passed to the caller.
- Statements executed for database operations, including statements that call other stored procedures;
- The status value returned to the caller to indicate whether the call is successful or failed.
12.2.1 use Enterprise Manager to create a stored procedure
Follow these steps to create a stored procedure with Enterprise Manager:
- Start Enterprise Manager and log on to the server to be used.
- Select the database for which you want to create a stored procedure, and click the Stored Procedure folder in the left pane. All stored procedures of the database are displayed in the right pane, as shown in 12-1.
- Right-click the Stored Procedure folder and select new stored procedure from the shortcut menu. The create Stored Procedure dialog box is displayed, as shown in 12-2.
- Enter the body of the stored procedure.
- Click Check syntax to check whether the syntax is correct.
- Click OK to save.
- In the right pane, right-click the stored procedure, select all task in the pop-up menu, select managepermissions, and set permissions, as shown in 12-3.
12.2.2 use the create procedure command to create a stored procedure
You can use the create procedure command to create a stored procedure. Before creating a stored procedure, consider the following aspects:
- In a batch, the create procedure statement cannot be combined with other SQL statements;
- The database owner has the default permission to create a stored procedure, which can pass this permission to other users;
- The name of a stored procedure as a database object must comply with the naming rules;
- You can only create stored procedures that belong to the current database in the current database.
The syntax rules for using CREATE procedure to create a stored procedure are as follows:
Create proc [edure] procedure_name [; number]
[{@ Parameter data_type}
[Varying] [= default] [Output]
] [,... N]
[
{Recompile | encryption | recompile, encryption}]
[For replication]
As SQL _statement [... n]
The meanings of parameters are as follows:
- Procedure_name
Is the name of the stored procedure to be created. It is followed by an option number, which is an integer to distinguish a set of stored procedures with the same name. The name of a stored procedure must comply with the naming rules. In a database or its owner, the name of the stored procedure must be unique.
- @ Parameter
Is the parameter of the stored procedure. In the create procedure statement, one or more parameters can be declared. When calling this stored procedure, you must give all the parameter values unless the default value of the parameter is defined. If the parameter format is @ parameter = value, the order of parameters can be different. Otherwise, the user-provided parameter values must be consistent with the order of parameters in the parameter list. If a parameter is given in the form of @ parameter = value, other parameters must also be given in this form. A stored procedure can contain up to 1024 parameters.
- Data_type
Is the Data Type of the parameter. In a stored procedure, all data types, including text and image, can be used as parameters. However, the cursor data type can only be used as the output parameter. When defining the cursor data type, the varing and output keywords must also be defined. For output parameters that may be curstype data types, there is no limit on the maximum number of parameters.
- Varying
Specify the result set supported by the output parameter and apply it only to the cursor type parameter.
- Default
The default value of a parameter. If the default value is defined, the stored procedure can still be called even if no parameter value is provided. The default value must be a constant or a null value.
- Output
Indicates that this parameter is a return parameter. The output parameter can be used to return information to the caller. Text parameters cannot be used as output parameters.
- Recompile
It indicates that SQL server does not save the execution plan of the stored procedure, and the stored procedure needs to be re-compiled every time it is executed.
- Encryption
SQL Server encrypts the syscomments table. The text field of this table contains the Stored Procedure TEXT OF THE create procedure statement. You cannot view the Stored Procedure content by viewing the syscomments table using this keyword.
- For Replication
Option indicates that the stored procedure created for replication cannot be executed on the Ordering server, and is only executed when the stored procedure is created (only when data replication is performed ),. The for replication and with recompile options are incompatible.
- As
Specifies the action to be executed for the stored procedure.
- SQL _statement
Is an SQL statement that contains any quantity and type in the stored procedure.
In addition, it should be noted that the maximum size of a stored procedure is 128 MB, and the user-defined stored procedure must be created in the current database.
The following example describes how to create a stored procedure containing various reserved words.
