Create a stored procedure. The stored procedure is a set of statements that can receive and return user-supplied parameters.
Create a stored procedure. The stored procedure is a set of statements that can receive and return user-supplied parameters.
You can create a process for permanent use, or temporary use in a session (Partial temporary process), or temporary use in all sessions (Global temporary process ).
You can also create a stored procedure that runs automatically when Microsoft SQL Server is started.
Syntax
Create proc [EDURE] procedure_name [; number]
[{@ Parameter data_type}
[VARYING] [= default] [OUTPUT]
] [,... N]
[
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
[For replication]
AS SQL _statement [... n]
Parameters
Procedure_name
Name of the new stored procedure. The process name must comply with the identifier rules and must be unique to the database and its owner. For more information, see use identifiers.
To create a local temporary process, you can add a identifier (# procedure_name) before procedure_name. To create a global temporary process, you can add two identifier (# procedure_name) before procedure_name ). The complete name (including # Or #) cannot exceed 128 characters. The name of the specified process owner is optional.
; Number
Is an optional integer used to group processes with the same name, so that the same group of processes can be removed with a drop procedure statement. For example, the process used by an application named orders can be named orderproc; 1, orderproc; 2, and so on. The drop procedure orderproc statement removes the entire group. If the name contains a bound identifier, the number should not be included in the identifier and the appropriate delimiters should be used before and after procedure_name.
@ Parameter
Parameters in the process. One or more parameters can be declared in the create procedure statement. You must provide the value of each declared parameter during execution (unless the default value of this parameter is defined ). A stored procedure can have a maximum of 2.100 parameters.
Use the @ symbol as the first character to specify the parameter name. The parameter name must comply with the identifier rules. Each process parameter is only used for this process. The same parameter name can be used in other processes. By default, parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects. For more information, see EXECUTE.
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, the VARYING and OUTPUT keywords must also be specified. For more information about the data types and syntax provided by SQL Server, see data types.
It indicates that there is no limit on the maximum number of output parameters that can be cursor data types.
VARYING
Specify the result set supported by the output parameter (dynamically constructed by the stored procedure, and the content can be changed ). Only applicable to cursor parameters.
Default
The default value of the parameter. If the default value is defined, the process can be executed without specifying the value of this parameter. The default value must be a constant or NULL. If the LIKE keyword is used for this parameter, the default value can contain wildcards (%, _, [], and [^]).
OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC [UTE]. The OUTPUT parameter can be used to return information to the call process. The Text, ntext, and image parameters can be used as OUTPUT parameters. The OUTPUT parameter using the OUTPUT keyword can be a placeholder cursor.
It indicates that up to 2.100 parameter placeholders can be specified.
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE indicates that SQL Server does not cache the plan of the process, and the process will be re-compiled at runtime. Use the RECOMPILE option when you use an atypical or temporary value instead of overwriting the execution plan cached in the memory.
ENCRYPTION indicates the entries in the SQL Server encrypted syscomments table that contain the CREATE PROCEDURE statement text. ENCRYPTION prevents the process from being published as part of SQL Server replication.
It indicates that during the upgrade, SQL Server uses the encryption annotation stored in syscomments to recreate the encryption process.
FOR REPLICATION
The storage process created for replication cannot be executed on the subscription server .. Stored Procedures created using the for replication option can be used FOR filtering stored procedures and can only be executed during REPLICATION. This option cannot be used WITH the with recompile option.
AS
Specifies the operation to be performed in the process.
SQL _statement
Any number and types of Transact-SQL statements to be included in the process. But there are some restrictions.
Yes indicates that this process can contain placeholders for multiple Transact-SQL statements.
Note
The maximum size of stored procedures is 128 MB.
User-Defined stored procedures can only be created in the current database (except for temporary procedures, temporary procedures are always created in tempdb ). In a single statement, the create procedure statement cannot be combined with other Transact-SQL statements.
By default, the parameter can be blank. If the NULL parameter value is passed and this parameter is used in the CREATE or alter table statement, and the column referenced in this statement does not allow NULL, SQL Server generates an error message. To prevent passing a NULL parameter value to a column that does not allow NULL, add the programming logic to the process or use the DEFAULT value for the column (use the DEFAULT keyword of CREATE or alter table ).
We recommend that you explicitly specify NULL or not null for each column in any create table or alter table statement in the stored procedure, for example, when creating a temporary TABLE. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control how SQL Server assigns the NULL or NOT NULL attribute to a column (if NOT specified in the CREATE TABLE or ALTER TABLE statement ). If the storage process executed by a connection has different settings for these options than the connection settings for the creation process, the table columns created for the second connection may be empty, and show different behavior methods. If NULL or not null is explicitly declared for each column, a temporary table will be created using the same NULL for all connections that execute the stored procedure.
When creating or changing a stored procedure, SQL Server saves the settings SET QUOTED_IDENTIFIER and SET ANSI_NULLS. These original settings are used when the stored procedure is executed. Therefore, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings of all client sessions are ignored during the execution of the stored procedure. The SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that appear in the stored procedure do not affect the stored procedure function.
Other SET options (such as set arithabort, SET ANSI_WARNINGS, or SET ANSI_PADDINGS) are not saved when the stored procedure is created or changed. If the Stored Procedure logic depends on specific settings, you should add a SET statement at the beginning of the process to ensure that the settings are correct. When the SET statement is executed from the stored procedure, this setting is only valid before the stored procedure is completed. Then, the setting will be restored to the value when the stored procedure is called. This allows individual clients to set the required options without affecting the Stored Procedure logic.
Whether SQL Server interprets a null string as a single space or as a real Null String, which is controlled by compatibility level settings. If the compatibility level is less than or equal to 65, SQL Server interprets the Null String as a single space. If the compatibility level is 70, SQL Server interprets the Null String as a null string. For more information, see sp_dbcmptlevel.
Obtain information about stored procedures
To display the text used to create a process, execute sp_helptext in the database where the process is located and use the process name as a parameter.
The stored procedure created using the ENCRYPTION option cannot be viewed using sp_helptext.
To display reports on objects referenced by a process, use sp_depends.
To rename a process, use sp_rename.
Reference object
The stored procedure that SQL Server allows to reference an object that does not exist. During creation, only the syntax check is performed. If there is no effective plan in the cache during execution, compile the stored procedure to generate the execution plan. All objects referenced in the stored procedure are resolved only during compilation. Therefore, if a stored procedure with the correct syntax references a nonexistent object, it can still be created successfully, but it will fail during runtime because the referenced object does not exist. For more information, see latency name parsing and compilation.
Latency name resolution and Compatibility Level
SQL Server allows a stored procedure to reference a table that does not exist when it is created. This capability is called latency name resolution. However, if the Transact-SQL stored procedure references the table defined in the stored procedure, and the compatibility level setting (set by executing sp_dbcmptlevel) is 65, A warning will be issued during creation. If the referenced table does not exist at runtime, an error message is returned. For more information, see sp_dbcmptlevel and latency name parsing and compilation.
Execute the Stored Procedure
After the create procedure statement is successfully executed, the process name is stored in the sysobjects system table, and the text of the create procedure statement is stored in syscomments. During the first execution, the process is compiled to determine the optimal data access plan.
Cursor data type parameters
Stored procedures can only use the cursor data type for OUTPUT parameters. If you specify the cursor data type for a parameter, you must also specify the VARYING and OUTPUT parameters. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.
