CREATE PROCEDURE Statement

Creates a stored procedure (a precompiled collection of SQL statements) that can take and/or return user-supplied parameters. Stored procedures help to increase performance and consistency when performing repetitive tasks because stored procedures are compiled the first time they are executed. When executed again, subsequent run time is much shorter than for the equivalent set of stand-alone statements. Procedures can be created for permanent use or for temporary use within a user's session (local temporary procedure) or for temporary use within all user's sessions (global temporary procedure).

Syntax

CREATE PROCedure [owner.]procedure_name[;number]
    [(parameter1 [, parameter2]...[parameter255])]
[{FOR REPLICATION} | {WITH RECOMPILE}
    [{[WITH] | [,]} ENCRYPTION]]
AS sql_statements

where

procedure_name

Is the name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique within the database and its owner.

You can create local or global temporary procedures by preceding the procedure_name with a single pound sign (#table_name) for local temporary procedures and a double pound sign (##table_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 20 characters. For more information, see "Temporary Stored Procedures," later in this topic.

Stored procedures can also be created for auto execution. Auto execution stored procedures are run automatically when SQL Server starts. For more information, see "Auto Execution Stored Procedures," later in this topic.

;number

Is an optional integer used to group procedures of the same name so that they can be dropped together with a single DROP PROCEDURE statement. When grouping is used with a procedure whose name was created using quoted identifiers, the number should not be included as part of the identifier; use double quotation marks only around the procedure_name. Procedures used in the same application are often grouped this way. For example, the procedures used with the application orders might be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. Once procedures have been grouped, individual procedures within the group cannot be dropped.

parameter =

@parameter_name datatype [= default] [OUTPUT]

parameter_name

Specifies a parameter in the procedure. One or more parameters can optionally be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter has been defined). A stored procedure can have a maximum of 255 parameters.

The first character of a parameter name must be the "at" symbol (@) and conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see the EXECUTE statement.

datatype

Specifies the datatype of the parameter. All datatypes except image are supported. For a list of SQL Server - supplied datatypes and their syntax, see the Datatypes topic.

default

Specifies a default value for the parameter. If a default is defined, a user can execute the procedure without specifying a value for that parameter. The default must be a constant. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword. The default can be NULL. The procedure definition can specify that some action be taken if the parameter value is NULL.

OUTPUT

Indicates that the parameter is a return parameter. The value of this option can be returned to the EXECUTE statement that called the procedure. Use return parameters to return information to the calling procedure. Text parameters cannot be used as OUTPUT parameters.

FOR REPLICATION

Is mutually exclusive of the WITH RECOMPILE option. Stored procedures created for replication cannot be executed on the subscribing server. FOR REPLICATION is used when creating a filter stored procedure that will be executed only by replication.

RECOMPILE

Indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled each time it is executed. Use this optional clause when you expect that the parameters you supply to the procedure won't be typical (that is, they won't result in the same optimal plan each time the procedure is executed). This can be beneficial when using wildcards in passing parameters because the distribution may be drastically different depending on the number of matches with the wildcard pattern.

Note If any constraints are added or altered, any stored procedures that reference the table will be automatically recompiled.

ENCRYPTION

Encrypts the syscomments table entry that contains the text of the CREATE PROCEDURE statement.

Important When a database is upgraded, syscomments entries are required in order to re-create procedures. Use encryption only when absolutely necessary; never delete entries from syscomments.

AS sql_statements

Specifies the actions the procedure is to take. Any number and type of SQL statements can be included in the procedure; however, there are some general limitations. For more information, see "sql_statement Limitations," later in this topic.

Remarks

When defining a stored procedure, you can create a stored procedure only in the current database (except for temporary procedures, which are always created in tempdb), and the CREATE PROCEDURE statement cannot be combined with other SQL statements in a single batch.

Stored procedures can use nesting, when one stored procedure calls another. The nesting level is incremented when the called procedure starts execution, and it is decremented when the called procedure finishes execution. Exceeding the maximum of 16 levels of nesting causes the whole calling procedure chain to fail. The current nesting level is stored in the @@NESTLEVEL global variable.

The maximum amount of text in a stored procedure is 65,025 characters. This limit is imposed because the text is stored in the syscomments system tables, where each procedure can occupy 255 rows of 255 bytes each (255 * 255 = 65,025).

To estimate the size of a compiled stored procedure, use DBCC MEMUSAGE. For details, see the DBCC statement.