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]
I. Introduction of 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 procedure, you can add a number character (#procedure_name) in front of the procedure_name, and to create a global staging procedure, you can add two number characters (# #procedure_name) before the procedure_name. The full name (including # or # #) cannot exceed 128 characters. Specifies that the name of the procedure owner 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 procedure that can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement will drop the entire group. If the name contains 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 parameters in the procedure. You can declare one or more parameters in the Create PROCEDURE statement. The user must provide the value of each declared parameter when the procedure is executed (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. Parameter names must conform to the rules for identifiers. The parameters for each procedure are used only for the procedure itself, and the same parameter names can be used in other procedures. By default, a parameter can be used instead of a constant instead of the name of a table name, column name, or other database object.
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, you must also specify both the VARYING and the OUTPUT keywords.
Description: There is no maximum limit for 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 the cursor parameter is applicable.
6. Default
The default value of the parameter. If you define a default value, you do not have to specify the value of the parameter to perform the procedure. The default value must be constant or NULL. If the procedure uses the LIKE keyword for the parameter, the default value can include wildcards (%, _, [], and [^]).
7. OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to Exec[ute]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. The output parameter that is used with the Export keyword can be a cursor placeholder.
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 recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and you do not want to overwrite the execution plan that is cached in memory.
Encryption represents an entry in SQL Server encrypted syscomments table that contains the text of the Create PROCEDURE statement. Use encryption to prevent the process from being published as part of SQL Server replication.
Description: During the upgrade process, SQL Server re-creates the encryption process by leveraging the cryptographic annotations stored in syscomments.
10. For REPLICATION
Specifies that stored procedures created for replication cannot be performed at the Subscriber: stored procedures created with the For REPLICATION option can be used as a stored procedure filter and can only be performed during replication. This option cannot be used with the WITH RECOMPILE option.
11, as
Specifies the action to be performed by the procedure.
12, Sql_statement
Any 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 contain more than one Transact-SQL statement.
14. Comments
A comment between/* and * * can contain one line and multiple lines of descriptive text.
15. Other Instructions
The maximum size of a stored procedure is MB.
Second, small bet:
Stored procedures are generally used to complete data querying and processing operations, so the statement that creates a database object cannot be used in a stored procedure, that is, the following statements are generally not included in the stored procedure:
Create TABLE, create VIEW, create DEFAULT, create RULE, create TRIGGER; Create PROCEDURE
