schema_name

Is the name of the schema to which the procedure belongs.

procedure_name

Is the name of the new stored procedure. Procedure names must comply with the rules for identifiers and must be unique within the schema.

We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures. For more information, see Creating Stored Procedures (Database Engine).

Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). Temporary names cannot be specified for CLR stored procedures.

The complete name for a stored procedure or a global temporary stored procedure, including ##, cannot exceed 128 characters. The complete name for a local temporary stored procedure, including #, cannot exceed 116 characters.

; number

Is an optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement. For example, an application called orders might use procedures named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement drops the whole group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around only procedure_name.

Numbered stored procedures have the following restrictions:

• Cannot use xml or CLR user-defined types as the data types.
  
• Cannot create a plan guide on a numbered stored procedure.
  

Note:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

@ parameter

Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is called, unless a default for the parameter is defined or the value is set to equal another parameter. A stored procedure can have a maximum of 2,100 parameters. If a procedure contains table-valued parameters, and the parameter is missing in the call, a default of empty table is passed in.

Specify a parameter name by using an at sign (@) as the first character. The parameter name must comply with 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 constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. For more information, see EXECUTE (Transact-SQL).

Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name. ] data_type

Is the data type of the parameter and the schema to which it belongs. All data types, can be used as a parameter for a Transact-SQL stored procedure. You can use a user-defined table type to declare a table-valued parameter as to a parameter for a Transact-SQL stored procedure. Table-valued parameters can be specified as input parameters only, and they must be accompanied by a READONLY keyword. The cursor data type can be used only on OUTPUT parameters. When you specify a cursor data type, the VARYING and OUTPUT keywords must also be specified. You can have multiple output parameters specified with the cursor data type.

For CLR stored procedures, char, varchar, text, ntext, image, cursor, user-define table types and table cannot be specified as parameters. For more information about the correspondence between CLR types and SQL Server system data types, see Mapping CLR Parameter Data. For more information about SQL Server system data types and their syntax, see Data Types (Transact-SQL).

If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

If type_schema_name is not specified, the SQL Server Database Engine references type_name in the following order:

• The SQL Server system data types.
  
• The default schema of the current user in the current database.
  
• The dbo schema in the current database.
  

For numbered stored procedures, the data type cannot be xml or a CLR user-defined type.

VARYING

Specifies the result set supported as an output parameter. This parameter is dynamically constructed by the stored procedure and its contents may vary. Applies only to cursor parameters.

default

Is a default value for the parameter. If a default value is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. If the procedure uses the parameter with the LIKE keyword, it can include the following wildcard characters: % _ [] and [^].

Note:
Default values are recorded in the sys.parameters.default column only for CLR procedures. That column will be NULL for Transact-SQL procedure parameters.

OUTPUT

Indicates that the parameter is an output parameter. The value of this option can be returned to the calling EXECUTE statement. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure. A user-defined table type cannot be specified as an OUTPUT parameter of a stored procedure.

READONLY

Indicate that the parameter cannot be updated or modified within the body of the procedure. If the parameter type is a user-defined table type, READONLY must be specified.

RECOMPILE

Indicates that the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time. This option cannot be used when FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored procedures.

To instruct the Database Engine to discard plans for individual queries inside a stored procedure, use the RECOMPILE query hint. For more information, see Query Hints (Transact-SQL). Use the RECOMPILE query hint when atypical or temporary values are used in only a subset of queries that belong to the stored procedure.

ENCRYPTION

Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.

This option is not valid for CLR stored procedures.

Procedures created by using this option cannot be published as part of SQL Server replication.

EXECUTE AS

Specifies the security context under which to execute the stored procedure.

For more information, see EXECUTE AS Clause (Transact-SQL).

FOR REPLICATION

Specifies that stored procedures that are created for replication cannot be executed on the Subscriber. A stored procedure created with the FOR REPLICATION option is used as a stored procedure filter and is executed only during replication. Parameters cannot be declared if FOR REPLICATION is specified. FOR REPLICATION cannot be specified for CLR stored procedures. The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

A FOR REPLICATION procedure will have an object type RF in sys.objects and sys.procedures.

<sql_statement>

Is one or more Transact-SQL statements to be included in the procedure. For information about some limitations that apply, see the Remarks section.

EXTERNAL NAME assembly_name.class_name.method_name

Specifies the method of a .NET Framework assembly for a CLR stored procedure to reference. class_name must be a valid SQL Server identifier and must exist as a class in the assembly. If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([ ]) or quotation marks (" "). The specified method must be a static method of the class.

Note:
By default, SQL Server cannot execute CLR code. You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL Server until you enable the clr enabled option. To enable the option, use sp_configure.

There is no predefined maximum size of a stored procedure.

A user-defined stored procedure can be created only in the current database. Temporary procedures are an exception to this because they are always created in tempdb. If a schema name is not specified, the default schema of the user that is creating the procedure is used. For more information about schemas, see User-Schema Separation.

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

By default, parameters are nullable. If a NULL parameter value is passed and that parameter is used in a CREATE TABLE or ALTER TABLE statement in which the referenced column does not allow null values, the Database Engine generates an error. To prevent passing NULL to a column that does not allow for null values, add programming logic to the procedure or use a default value for the column by using the DEFAULT keyword of CREATE TABLE or ALTER TABLE.

We recommend that you explicitly specify NULL or NOT NULL for each column in a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a stored procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the stored procedure.

Using SET Options

The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL stored procedure is created or modified. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the stored procedure is running. Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or modified. If the logic of the stored procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure has finished running. The setting is then restored to the value the stored procedure had when it was called. This enables individual clients to set the options they want without affecting the logic of the stored procedure.

Note:
ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Using Parameters with CLR Stored Procedures

Getting Information About Stored Procedures

To display the definition of a Transact-SQL stored procedure, use the sys.sql_modules catalog view in the database in which the procedure exists.

For example:

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

Note:
The text of a stored procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.

For a report about the objects referenced by a procedure, query the sys.sql_expression_dependencies catalog view or use sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

To display information about CLR stored procedures, use the sys.assembly_modules catalog view in the database in which the procedure exists.

To display information about the parameters that are defined in a stored procedure, use the sys.parameters catalog view in the database in which the procedure exists.

Deferred Name Resolution

Executing Stored Procedures

Parameters That Use the cursor Data Type

Temporary Stored Procedures

Automatically Executing Stored Procedures

Stored Procedure Nesting

<sql_statement> Limitations

Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

For CLR stored procedures, requires ownership of the assembly referenced in <method_specifier>, or REFERENCES permission on that assembly.

A. Using a simple procedure

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. Using a simple procedure with parameters

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

C. Using a simple procedure with wildcard parameters

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName 
        AND LastName LIKE @LastName;
GO

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

D. Returning more than one result set

USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

E. Using OUTPUT parameters

The following example creates the uspGetList stored procedure. This procedures returns a list of products that have prices that do not exceed a specified amount. The example shows using multiple SELECT statements and multiple OUTPUT parameters. OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQL statement to access a value set during the procedure execution.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Execute uspGetList to return a list of Adventure Works products (Bikes) that cost less than $700. The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.

Note:
The OUTPUT variable must be defined when the procedure is created and also when the variable is used. The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice = variable is used.

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Here is the partial result set:

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

F. Using the WITH RECOMPILE option

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

G. Using the WITH ENCRYPTION option

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

EXEC sp_helptext 'HumanResources.uspEncryptThis';

The text for object 'HumanResources.uspEncryptThis' is encrypted.

USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

definition
----------------------
NULL

(1 row(s) affected)

H. Using deferred name resolution

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SELECT column1, column2 FROM table_does_not_exist
GO

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

I. Using the EXECUTE AS clause

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

J. Creating a CLR stored procedure

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll'';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID int,
    @CurrentDirectory nvarchar(1024),
    @FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

K. Using an OUTPUT cursor parameter

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

Reference

Other Resources

Help and Information