SQL Server Stored Procedure Syntax: CreatePROC [EDURE] procedure_name [; number] [{@ parameterdata_type} [VARYING] [default] [OUTPUT] [,... n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FORREPLICATION] ASsql_statement [... n] 1. Parameter Introduction 1. procedure _
SQL SERVER Stored Procedure Syntax: CreatePROC [EDURE] procedure_name [; number] [{@ parameterdata_type} [VARYING] [= default] [OUTPUT] [,... n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FORREPLICATION] ASsql_statement [... n] 1. Parameter Introduction 1. procedure _
SQL SERVER Stored Procedure Syntax:
Create PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]1. Parameter Introduction
1. procedure_name
Name of the new stored procedure. The process name must comply with the identifier rules and must be unique to the database and its owner.
To create a local temporary process, you can add a identifier (# procedure_name) before procedure_name. To create a global temporary process, you can add two identifier (# procedure_name) before procedure_name ). The complete name (including # Or #) cannot exceed 128 characters. The name of the specified process owner is optional.
2.; number
Is an optional integer used to group processes with the same name, so that the same group of processes can be removed with a Drop PROCEDURE statement. For example, the process used by an application named orders can be named orderproc; 1, orderproc; 2, and so on. The Drop PROCEDURE orderproc statement removes the entire group. If the name contains a bound identifier, the number should not be included in the identifier and the appropriate delimiters should be used before and after procedure_name.
3. @ parameter
Parameters in the process. One or more parameters can be declared in the Create PROCEDURE statement. You must provide the value of each declared parameter during execution (unless the default value of this parameter is defined ). A stored procedure can have a maximum of 2100 parameters.
Use the @ symbol as the first character to specify the parameter name. The parameter name must comply with the identifier rules. Each process parameter is only used for this process. The same parameter name can be used in other processes. By default, parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects.
4. data_type
The data type of the parameter. All data types (including text, ntext, and image) can be used as parameters for stored procedures. However, the cursor data type can only be used for OUTPUT parameters. If the specified data type is cursor, the VARYING and OUTPUT keywords must also be specified.
Note: There is no limit on the maximum number of output parameters that can be cursor data types.
5. VARYING
Specify the result set supported by the output parameter (dynamically constructed by the stored procedure, and the content can be changed ). Only applicable to cursor parameters.
6. default
The default value of the parameter. If the default value is defined, the process can be executed without specifying the value of this parameter. The default value must be a constant or NULL. If the LIKE keyword is used for this parameter, the default value can contain wildcards (%, _, [], and [^]).
7. OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC [UTE]. The OUTPUT parameter can be used to return information to the call process. The Text, ntext, and image parameters can be used as OUTPUT parameters. The OUTPUT parameter using the OUTPUT keyword can be a placeholder cursor.
8. n
It indicates that up to 2100 parameter placeholders can be specified.
9. {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE indicates that SQL Server does not cache the plan of the process, and the process will be re-compiled at runtime. Use the RECOMPILE option when you use an atypical or temporary value instead of overwriting the execution plan cached in the memory.
ENCRYPTION indicates the entries in the SQL Server encrypted syscomments table that contain the Create PROCEDURE statement text. ENCRYPTION prevents the process from being published as part of SQL Server replication.
Note: During the upgrade, SQL Server uses the encryption notes stored in syscomments to recreate the encryption process.
10. FOR REPLICATION
The storage process created for replication cannot be executed on the subscription server .. Stored Procedures created using the for replication option can be used FOR filtering stored procedures and can only be executed during REPLICATION. This option cannot be used WITH the with recompile option.
11.
Specifies the operation to be performed in the process.
12. SQL _statement
Any number and types of Transact-SQL statements to be included in the process. But there are some restrictions.
13. n
Yes indicates that this process can contain placeholders for multiple Transact-SQL statements.
14. Notes
The comments between/* and */can contain one line and multiple lines of explanatory text.
15. Other Instructions
The maximum size of stored procedures is 128 MB.
Ii. Note:
Stored procedures are generally used to perform data query and data processing operations. Therefore, you cannot use statements for creating database objects during the stored procedures. That is, the stored procedures generally do not contain the following statements:
CREATE TABLE ； CREATE VIEW ； CREATE DEFAULT ；CREATE RULE ；CREATE TRIGGER ；CREATE PROCEDURE
