Create | Stored procedures in MS SQL Server 2000, there are two ways to create a stored procedure: one is using the Transaction-sql command create Procedure, and the other is using a graphical administration tool Enterprise Manager. Creating stored procedures with Transaction-sql is a faster approach, but for beginners it is easier to understand and use enterprise Manager.
When creating a stored procedure, you need to determine the three components of the stored procedure;
- All input parameters and the output parameters passed to the caller.
- An operational statement executed against the database, including statements that invoke other stored procedures;
- Returns the state value of the caller to indicate that the call is о?/li>
12.2.1 Use Enterprise Manager to create a stored procedure
Follow the steps below to create a stored procedure with enterprise Manager:
- Start Enterprise Manager and log on to the server you want to use.
- Select the database where you want to create the stored procedure, click the stored Procedure folder in the left pane, and then display all stored procedures for that database in the right pane, as shown in Figure 12-1.
- Right-click the Stored Procedure folder, select New Stored Procedure in the pop-up menu, and open the Create Stored Procedure dialog box, as shown in Figure 12-2.
- Enter the body of the stored procedure.
- Click Check Syntax to verify that 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 Figure 12-3.
12.2.2 creating a stored procedure with the Create PROCEDURE command
You can create stored procedures by using the Create Procedure command, and before you create a stored procedure, you should consider the following:
- In a batch, the Create Procedure statement cannot be merged with other SQL statements;
- The database owner has the default permission to create a stored procedure that can pass that permission to another user;
- The stored procedure, as a database object, must conform to the naming rules;
- Only stored procedures that belong to the current database can be created in the current database.
The syntax rules for creating stored procedures with Create Procedure are as follows:
CREATE PROC [edure] procedure_name [; number]
[{@parameter data_type}
[Varying] [= default] [OUTPUT]
] [,... N]
[With
{RECOMPILE | Encryption | RECOMPILE, encryption}]
[For REPLICATION]
As sql_statement [... n]
The meanings of the parameters are as follows:
- Procedure_name
is the name of the stored procedure to be created, followed by an option number, which is an integer that distinguishes a set of stored procedures with the same name. The name of a stored procedure must conform to a naming convention, and in a database or its owner, the stored procedure must be named uniquely.
- @parameter
is the parameter of the stored procedure. In the Create PROCEDURE statement, you can declare one or more parameters. When the stored procedure is invoked, the user must give all parameter values, unless the default value of the parameter is defined. If the form of the parameter appears in @parameter =value, the order of the parameters can be different, otherwise the parameter values given by the user must be consistent with the order of the parameters in the argument list. If a parameter is given in @parameter=value form, then other parameters must also be given in that form. A stored procedure has a maximum of 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 cursor data type can only be used as an output parameter. When you define a cursor data type, you must also define the varing and output keywords. There is no limit to the maximum number of parameters for an output parameter that may be a cursor-type data type.
- Varying
Specifies the result set supported by the output parameter, with only the cursor type parameter applied.
- Default
Refers to the default value of the parameter. If a default value is defined, the stored procedure can still be invoked, even if the parameter value is not given. The default value must be a constant or a null value.
- OUTPUT
Indicates that the parameter is a return parameter. Use the output parameter to return information to the caller. The Text type parameter cannot be used as an output parameter.
- RECOMPILE
Indicates that SQL Server does not save the execution plan for the stored procedure, and that the stored procedure is recompiled every time it executes.
- Encryption
Indicates that SQL Server encrypted the syscomments table with the text field of the stored procedure containing the CREATE PROCEDURE statement, which cannot be used to view the contents of the stored procedure by viewing the syscomments table.
- For REPLICATION
option indicates that the stored procedure created for replication cannot be performed on the subscription server and is used only when the filter stored procedure is created (the filter stored procedure is executed only when data replication is performed). The for replication is incompatible with the WITH RECOMPILE option.
- As
Indicates the action that the stored procedure will perform.
- Sql_statement
Is any number and type of SQL statement contained in a stored procedure.
It should also be noted that the maximum size of a stored procedure is 128M, and user-defined stored procedures must be created in the current database.
Here are a few examples that detail how to create a stored procedure that contains a variety of reserved words.
