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]
First, a brief introduction of the parameters
1, procedure_name
The name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique to the database and all of them.
To create a local temporary process, you can add a number character (#procedure_name) before procedure_name, to create a global transient process that can precede the procedure_name with two number characters (# #procedure_name). The full name (contains # or # #) cannot exceed 128 characters. Specifies that the name of the whole person of the procedure is optional.
2. number
is an optional integer that is used to group procedures of the same name so that the same set of procedures can be removed with a Drop PROCEDURE statement. For example, an application named orders uses a process that can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement will drop the entire group. Assuming that the name includes a bounding identifier, the number should not be included in the identifier, only the appropriate delimiter should be used before and after procedure_name.
3. @parameter
The number of references in the process. One or more of the parameters can be declared in the Create PROCEDURE statement. The user must provide a value for each of the declared parameters when the procedure is run (unless the default value for that parameter is defined). A stored procedure can have a maximum of 2,100 parameters.
Use the @ symbol as the first character to specify a parameter name. The parameter name must conform to the rules of the identifier. The parameters of each process are used only for the process itself, and the same number of references can be used in other processes. By default, a parameter can only replace a constant, not the name of a delegate, column, or other database object.
4, Data_type
The data type of the number of parameters. All data types (including text, ntext, and image) can be used as the parameters of a stored procedure. Only, the cursor data type can only be used for OUTPUT parameters. Assuming that the specified data type is cursor, you must also specify VARYING and OUTPUT keyword at the same time.
Description: There is no maximum limit on the number of output parameters that can be the cursor data type.
5, VARYING
Specifies the result set that is supported as an output parameter (dynamically constructed by the stored procedure and the content can vary). Only cursor parameters are applicable.
6. Default
The default value of the number of parameters. Assume that a default value is defined and you do not have to specify the value of the parameter to run the procedure. The default value must be constant or NULL. Assuming that the procedure will use like keyword for that parameter, the default value can include wildcards (%, _, [], and [^]).
7. OUTPUT
Indicates that the number of references is the return parameter. The value of this option can be returned to Exec[ute]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. The output parameters that use outputs keyword can be cursor placeholders.
8, N
Represents a placeholder that can specify up to 2,100 parameters.
9. {RECOMPILE | Encryption | RECOMPILE, encryption}
RECOMPILE indicates that SQL Server does not cache the schedule for this procedure, which is compiled again at execution time. Use the RECOMPILE option when you are using atypical or transient values and you do not want to overwrite the execution plan that is cached in memory.
Encryption represents an entry in the SQL Server encryption syscomments table that includes the Create PROCEDURE statement text. Use encryption to prevent the process from being advertised as part of SQL Server replication.
Description: During the upgrade process, SQL Server created the encryption process again using the cryptographic gaze stored in syscomments.
10. For REPLICATION
Specifies that stored procedures created for replication cannot be run on the Subscriber: stored procedures created with the For REPLICATION option can be used as a stored procedure filter and can only be run during the replication process. This option cannot be used with the WITH RECOMPILE option.
11, as
Specifies the action to be run by the procedure.
12, Sql_statement
The random number and type of Transact-SQL statements to include in the procedure. But there are some limitations.
13, N
is a placeholder that indicates that this procedure can include multiple Transact-SQL statements.
14. Gaze
The gaze between/* and/* can include one line and multiple lines of explanatory text.
15. Other Instructions
The maximum size of a stored procedure is MB.
Second, small bet:
Stored procedures are generally used to complete data query and processing operations, so in the stored procedure can not be used to create a database object statement, that is, in the stored procedure generally cannot contain the following statement:
Create TABLE, create VIEW, create DEFAULT, create RULE, create TRIGGER; Create PROCEDURE
