SQL Server stored procedure syntax detailed

SQL Server stored procedure syntax:

Create PROC [edure] procedure_name [; number]  
    [{@parameter data_type}  
        [varying] [= default] [OUTPUT]< c3/>] [,... n]  
      
[with
    {RECOMPILE | Encryption | RECOMPILE, encryption}]  
      
[for REPLICATION] as  
      
sql_statement [... n]

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 precede procedure_name with a number character (#procedure_name), and to create a global temporary procedure, you can precede procedure_name with two number characters (# #procedure_name). The full name (including # or # #) cannot exceed 128 characters. Specifies that the name of the procedure owner is optional.

2.;

is an optional integer that is used to group procedures with the same name so that a drop PROCEDURE statement can be used to remove the same group of procedures. 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 drops the entire group. If the name contains a bounding identifier, the number should not be included in the identifier, and the appropriate delimiter should be used only 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 up to 2,100 parameters.

Use the @ symbol as the first character to specify the 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, parameters can only be substituted for constants and cannot be used in place of a table name, column name, or other database object name.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

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 you specify a data type of cursor, you must also specify both varying and OUTPUT keywords.

Note: There is no maximum number of restrictions for output parameters that can be cursor data types.

5, varying

Specifies the result set that is supported as output parameters (dynamically constructed by stored procedures and content can be changed). Only cursor parameters are applicable.

6, default

The default value for the parameter. If you define a default value, you do not have to specify the value of the parameter to execute the procedure. The default value must be constant or NULL. If the procedure uses the LIKE keyword for this parameter, the default value can contain wildcard characters (%, _, [], 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. Output parameters that use the input 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 the process, which will be recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and do not want to overwrite execution plans that are cached in memory.

Encryption represents an entry that contains the text of the Create PROCEDURE statement in the SQL Server encryption syscomments table. Use encryption to prevent the process from being published as part of SQL Server replication.

Description: During the upgrade process, SQL Server uses the encrypted annotations stored in syscomments to recreate the encryption process.

10, for REPLICATION

Specifies that stored procedures created for replication cannot be performed at the Subscriber. Stored procedures created using the For REPLICATION option can be used as stored procedure filtering and can only be performed during the replication process. 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 restrictions.

13, N

is to indicate that this procedure can contain placeholders for more than one Transact-SQL statement.

14, Notes

/* and/* is the annotation, can contain one line and multiple lines of descriptive text.

15, Other Notes

The maximum size of the stored procedure is 128 MB.

Second, note:

Stored procedures are typically used to complete data querying and processing operations, so statements that create database objects cannot be used in stored procedures, meaning that the following statements are generally not included in stored procedures:

Create TABLE; create VIEW; create DEFAULT; create Rule  
; create TRIGGER; Create PROCEDURE