Basic description of SQL Server stored procedures _mssql

You can create a procedure for permanent use, or for temporary use in a session (local temporary procedures), or for temporary use in all sessions (global temporary procedures).
You can also create stored procedures that run automatically when Microsoft SQL Server starts.
Grammar
CREATE PROC [edure] procedure_name [; number]
[{@parameter data_type}
[Varying] [= default] [OUTPUT]
] [,... N]
[With
{RECOMPILE | Encryption | RECOMPILE, encryption}]
[For REPLICATION]
As sql_statement [... n]
Parameters
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. For more information, see Using Identifiers.
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.
; number
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.
@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.1 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. 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 you specify a data type of cursor, you must also specify both varying and OUTPUT keywords. For more information about the data types and their syntax provided by SQL Server, see Data types.
Indicates that there is no maximum number of restrictions for output parameters that can be cursor data types.
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.
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 [^]).
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.

Represents a placeholder that can specify up to 2.1 parameters.
{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.
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.
As
Specifies the action to be performed by the procedure.
Sql_statement
Any number and type of Transact-SQL statements to include in the procedure. But there are some restrictions.

is to indicate that this procedure can contain placeholders for more than one Transact-SQL statement.
Comments
The maximum size of the stored procedure is 128 MB.
A user-defined stored procedure can only be created in the current database (except for temporary procedures, which are always created in tempdb). In a single batch, the CREATE PROCEDURE statement cannot be used in combination with other Transact-SQL statements.
By default, the parameter can be empty. If you pass a null parameter value and the parameter is used in a CREATE or ALTER TABLE statement, and the column referenced in the statement does not allow NULL, SQL Server generates an error message. To prevent a null parameter value from being passed to a column that does not allow NULL, you should add programming logic to the procedure or use the default value for the column (using the defaults keyword of CREATE or ALTER TABLE).
It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER table statement in the stored procedure, such as when you create a temporary table. The ansi_dflt_on and Ansi_dflt_off options control how SQL Server assigns a null or NOT NULL attribute to a column (if not specified in the CREATE table or ALTER table statement). If a connection executes a stored procedure that sets these options differently than the connection that created the procedure, the table columns created for the second connection may have different nullability and behave differently. If NULL or NOT NULL is explicitly declared for each column, temporary tables are created with the same nullability for all connections that execute the stored procedure.
When you create or change a stored procedure, SQL Server saves the settings for 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 for all client sessions are ignored when the stored procedure is executed. The SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur during a stored procedure do not affect the functionality of the stored procedure.
Other set options, such as set ARITHABORT, set ANSI_WARNINGS, or set ansi_paddings, are not saved when a stored procedure is created or changed. If the logic of a stored procedure depends on a specific setting, add a set statement at the beginning of the procedure to ensure that it is set correctly. When a set statement is executed from a stored procedure, the setting is valid only until the stored procedure completes. After that, the setting reverts to the value when the stored procedure was invoked. This allows individual clients to set the desired options without affecting the logic of the stored procedure.
Describes whether SQL Server interprets an empty string as a single space or is interpreted as a true empty string, controlled by the compatibility level setting. An empty string is interpreted as a single space if the compatibility level is less than or equal to 65,sql Server. If the compatibility level equals 70, SQL Server interprets the empty string as an empty string. For more information, see sp_dbcmptlevel.
To get information about stored procedures
To display the text used to create the procedure, execute sp_helptext in the database in which the procedure is located and use the procedure name as a parameter.
Explains that stored procedures created using the encryption option cannot be viewed using sp_helptext.
To display a report about the objects referenced by the procedure, use sp_depends.
To rename a procedure, use sp_rename.
referencing objects
SQL Server allows you to create stored procedures that reference objects that do not already exist. When created, only syntax checking is performed. When executed, the stored procedure is compiled to generate an execution plan if there is no valid schedule in the cache. All objects referenced in the stored procedure are parsed only during compilation. Therefore, if the syntactically correct stored procedure references a nonexistent object, it can still be created successfully, but will fail at run time because the referenced object does not exist. For more information, see deferred name resolution and compilation.
Deferred name resolution and compatibility levels
SQL SERVER allows Transact-SQL stored procedures to reference tables that do not exist when they are created. This ability is called deferred name resolution. However, if a Transact-SQL stored procedure references a table defined in the stored procedure, and the compatibility level setting (set by executing sp_dbcmptlevel) is 65, a warning message is issued when it is created. If the table referenced at run time does not exist, an error message is returned. For more information, see sp_dbcmptlevel and deferred name resolution and compilation.
Executing stored procedures
After the Create PROCEDURE statement is executed successfully, the procedure name is stored in the sysobjects system table, and the text of the Create PROCEDURE statement is stored in the syscomments. On first execution, the process is compiled to determine the best access plan for retrieving data.
Parameters using the cursor data type
Stored procedures can only use the cursor data type for OUTPUT parameters. If you specify a cursor data type for a parameter, you must also specify the varying and OUTPUT parameters. If you specify a varying keyword for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.