Specific explanations of SQL Server stored procedure syntax

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 procedure, you can add a number character (#procedure_name) to the front of the procedure_name to create a global transient procedure. Ability to add two number characters (# #procedure_name) in front of 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. Used to group procedures of the same name so that the same set of procedures can be removed with a Drop PROCEDURE statement. Like what. The process used by an application named orders 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: For output parameters that can be a cursor data type. There is no maximum number of restrictions.
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. It is assumed that the process will use like keyword for that parameter. The wildcard characters (%, _, [], and [^]) can be included in the default values.

7. OUTPUT
Indicates that the number of references is the return parameter. The value of this option can be returned to Exec[ute].

The

uses 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 A
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 the procedure, and that the process will compile 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. The
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 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 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

Copyright: Author: jiankunking Resources: http://blog.csdn.net/jiankunking This article is copyrighted by the author and Csdn jointly owned. Welcome reprint. However, if the commit permission is not declared from this section, it must be persisted. and connect to the original article page to give a view of the location.

Specific explanations of SQL Server stored procedure syntax