CREATE PROCEDURE (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Creates a Transact-SQL or common language runtime (CLR) stored procedure in SQL Server, Azure SQL Database, and Analytics Platform System (PDW). Stored procedures are similar to procedures in other programming languages in that they can:
- Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- Contain programming statements that perform operations in the database, including calling other procedures.
- Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb
database.
Note
The integration of .NET Framework CLR into SQL Server is discussed in this topic. CLR integration does not apply to Azure SQL Database.
Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.
Transact-SQL syntax conventions
Syntax
Transact-SQL syntax for stored procedures in SQL Server and Azure SQL Database:
CREATE [ OR ALTER ] { PROC | PROCEDURE }
[schema_name.] procedure_name [ ; number ]
[ { @parameter_name [ type_schema_name. ] data_type }
[ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
Transact-SQL syntax for CLR stored procedures:
CREATE [ OR ALTER ] { PROC | PROCEDURE }
[schema_name.] procedure_name [ ; number ]
[ { @parameter_name [ type_schema_name. ] data_type }
[ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]
Transact-SQL syntax for natively compiled stored procedures:
CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name
[ { @parameter data_type } [ NULL | NOT NULL ] [ = default ]
[ OUT | OUTPUT ] [READONLY]
] [ ,... n ]
WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]
AS
{
BEGIN ATOMIC WITH ( <set_option> [ ,... n ] )
sql_statement [;] [ ... n ]
[ END ]
}
[;]
<set_option> ::=
LANGUAGE = [ N ] 'language'
| TRANSACTION ISOLATION LEVEL = { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }
| [ DATEFIRST = number ]
| [ DATEFORMAT = format ]
| [ DELAYED_DURABILITY = { OFF | ON } ]
Transact-SQL syntax for stored procedures in Azure Synapse Analytics and Parallel Data Warehouse:
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name
[ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
[ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]
Transact-SQL syntax for stored procedures in Microsoft Fabric:
CREATE [ OR ALTER ] { PROC | PROCEDURE } [ schema_name.] procedure_name
[ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
[ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
OR ALTER
Applies to: Azure SQL Database, SQL Server (starting with SQL Server 2016 (13.x) SP1).
Alters the procedure if it already exists.
schema_name
The name of the schema to which the procedure belongs. Procedures are schema-bound. If a schema name isn't specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.
procedure_name
The name of the procedure. Procedure names must comply with the rules for identifiers and must be unique within the schema.
Caution
Avoid the use of the sp_
prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.
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). A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. Temporary names can't be specified for CLR procedures.
The complete name for a procedure or a global temporary procedure, including ##
, can't exceed 128 characters. The complete name for a local temporary procedure, including #
, can't exceed 116 characters.
; number
Applies to: SQL Server 2008 (10.0.x) and later, and Azure SQL Database.
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.
Note
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Numbered procedures can't use the xml or CLR user-defined types and can't be used in a plan guide.
@parameter_name
A parameter declared in the procedure. Specify a parameter name by using the 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.
One or more parameters can be declared; the maximum is 2,100. The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. Parameters can take the place only of constant expressions; they can't be used instead of table names, column names, or the names of other database objects. For more information, see EXECUTE (Transact-SQL).
Parameters can't be declared if FOR REPLICATION is specified.
[ type_schema_name. ] data_type
The data type of the parameter and the schema to which the data type belongs.
Guidelines for Transact-SQL procedures:
- All Transact-SQL data types can be used as parameters.
- You can use the user-defined table type to create table-valued parameters. Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. For more information, see Use Table-Valued Parameters (Database Engine)
- cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.
Guidelines for CLR procedures:
All of the native SQL Server data types that have an equivalent in managed code can be used 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).
Table-valued or cursor data types can't be used as parameters.
If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.
VARYING
Specifies the result set supported as an output parameter. This parameter is dynamically constructed by the procedure and its contents may vary. Applies only to cursor parameters. This option isn't valid for CLR procedures.
default
A default value for a parameter. If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. The default value must be a constant or it can be NULL. The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.
Default values are recorded in the sys.parameters.default
column only for CLR procedures. That column is NULL for Transact-SQL procedure parameters.
OUT | OUTPUT
Indicates that the parameter is an output parameter. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters can't be used as OUTPUT parameters, unless the procedure is a CLR procedure. An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. A table-value data type can't be specified as an OUTPUT parameter of a procedure.
READONLY
Indicates that the parameter can't be updated or modified within the body of the procedure. If the parameter type is a table-value type, READONLY must be specified.
RECOMPILE
Indicates that the Database Engine doesn't cache a query plan for this procedure, forcing it to be compiled each time it is executed. For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. This option can't be used when FOR REPLICATION is specified or for CLR procedures.
To instruct the Database Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. For more information, see Query Hints (Transact-SQL).
ENCRYPTION
Applies to: SQL Server ( SQL Server 2008 (10.0.x) and later), Azure SQL Database.
Indicates that SQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation isn't directly visible in any of the catalog views in SQL Server. Users who have no access to system tables or database files can't retrieve the obfuscated text. However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. Also, users who 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 isn't valid for CLR procedures.
Procedures created with this option can't be published as part of SQL Server replication.
EXECUTE AS clause
Specifies the security context under which to execute the procedure.
For natively compiled stored procedures, starting SQL Server 2016 (13.x) and in Azure SQL Database, there are no limitations on the EXECUTE AS clause. In SQL Server 2014 (12.x) the SELF, OWNER, and 'user_name' clauses are supported with natively compiled stored procedures.
For more information, see EXECUTE AS Clause (Transact-SQL).
FOR REPLICATION
Applies to: SQL Server ( SQL Server 2008 (10.0.x) and later), Azure SQL Database.
Specifies that the procedure is created for replication. Consequently, it can't be executed on the Subscriber. A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. Parameters can't be declared if FOR REPLICATION is specified. FOR REPLICATION can't be specified for CLR procedures. The RECOMPILE option is ignored for procedures created with FOR REPLICATION.
A FOR REPLICATION
procedure has an object type RF in sys.objects
and sys.procedures
.
{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
One or more Transact-SQL statements comprising the body of the procedure. You can use the optional BEGIN and END keywords to enclose the statements. For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.
EXTERNAL NAME assembly_name.class_name.method_name
Applies to: SQL Server 2008 (10.0.x) and later, SQL Database.
Specifies the method of a .NET Framework assembly for a CLR 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.
By default, SQL Server can't execute CLR code. You can create, modify, and drop database objects that reference common language runtime modules; however, you can't execute these references in SQL Server until you enable the clr enabled option. To enable the option, use sp_configure.
Note
CLR procedures are not supported in a contained database.
ATOMIC WITH
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Indicates atomic stored procedure execution. Changes are either committed or all of the changes rolled back by throwing an exception. The ATOMIC WITH block is required for natively compiled stored procedures.
If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. If the procedure THROWs, the work performed by the procedure is rolled back.
XACT_ABORT is ON by default inside an atomic block and can't be changed. XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
The following SET options are always ON in the ATOMIC block, and can't be changed.
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER, ARITHABORT
- NOCOUNT
- ANSI_NULLS
- ANSI_WARNINGS
SET options can't be changed inside ATOMIC blocks. The SET options in the user session aren't used in the scope of natively compiled stored procedures. These options are fixed at compile time.
BEGIN, ROLLBACK, and COMMIT operations can't be used inside an atomic block.
There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. The blocks can't be nested. For more information about atomic blocks, see Natively Compiled Stored Procedures.
NULL | NOT NULL
Determines whether null values are allowed in a parameter. NULL is the default.
NATIVE_COMPILATION
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Indicates that the procedure is natively compiled. NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. For more information, see Natively Compiled Stored Procedures.
SCHEMABINDING
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Ensures that tables that are referenced by a procedure can't be dropped or altered. SCHEMABINDING is required in natively compiled stored procedures. (For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).
LANGUAGE = [N] 'language'
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Equivalent to SET LANGUAGE (Transact-SQL) session option. LANGUAGE = [N] 'language' is required.
TRANSACTION ISOLATION LEVEL
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Required for natively compiled stored procedures. Specifies the transaction isolation level for the stored procedure. The options are as follows:
For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
REPEATABLE READ
Specifies that statements can't read data that has been modified but not yet committed by other transactions. If another transaction modifies data that has been read by the current transaction, the current transaction fails.
SERIALIZABLE
Specifies the following:
- Statements can't read data that has been modified but not yet committed by other transactions.
- If another transaction modifies data that has been read by the current transaction, the current transaction fails.
- If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.
SNAPSHOT
Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.
DATEFIRST = number
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Specifies the first day of the week to a number from 1 through 7. DATEFIRST is optional. If it isn't specified, the setting is inferred from the specified language.
For more information, see SET DATEFIRST (Transact-SQL).
DATEFORMAT = format
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2, and datetimeoffset character strings. DATEFORMAT is optional. If it isn't specified, the setting is inferred from the specified language.
For more information, see SET DATEFORMAT (Transact-SQL).
DELAYED_DURABILITY = { OFF | ON }
Applies to: SQL Server 2014 (12.x) and later, and Azure SQL Database.
SQL Server transaction commits can be either fully durable, the default, or delayed durable.
For more information, see Control Transaction Durability.
Simple examples
To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB;
returns the name of the current database.
You can wrap that statement in a stored procedure, such as:
CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;
Call the store procedure with statement: EXEC What_DB_is_this;
Slightly more complex, is to provide an input parameter to make the procedure more flexible. For example:
CREATE PROC What_DB_is_that @ID INT
AS
SELECT DB_NAME(@ID) AS ThatDB;
Provide a database ID number when you call the procedure. For example, EXEC What_DB_is_that 2;
returns tempdb
.
See Examples towards the end of this article for many more examples.
Best practices
Although this isn't an exhaustive list of best practices, these suggestions may improve procedure performance.
- Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. That is, place it just after the AS keyword. This turns off messages that SQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. This keeps the output generated to a minimum for clarity. There is no measurable performance benefit however on today's hardware. For information, see SET NOCOUNT (Transact-SQL).
- Use schema names when creating or referencing database objects in the procedure. It takes less processing time for the Database Engine to resolve object names if it doesn't have to search multiple schemas. It also prevents permission and access problems caused by a user's default schema being assigned when objects are created without specifying the schema.
- Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Doing so makes the columns non-deterministic and prevents the query processor from using indexes.
- Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.
- Avoid the use of
SELECT *
. Instead, specify the required column names. This can prevent some Database Engine errors that stop procedure execution. For example, aSELECT *
statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed. - Avoid processing or returning too much data. Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Send just the essential data to the client application. It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.
- Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. Longer transactions mean longer record locking and a greater potential for deadlocking.
- Use the Transact-SQL TRY...CATCH feature for error handling inside a procedure. TRY...CATCH can encapsulate an entire block of Transact-SQL statements. This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.
- Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the procedure. This prevents passing NULL to columns that don't allow null values.
- Use 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 aren't specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a 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 procedure.
- Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Be aware that in Transact-SQL, NULL isn't an empty or "nothing" value. It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.
- Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. The UNION ALL operator requires less processing overhead because duplicates aren't filtered out of the result set.
Remarks
There is no predefined maximum size of a procedure.
Variables specified in the procedure can be user-defined or system variables, such as @@SPID.
When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the Database Engine.
One or more procedures can execute automatically when SQL Server starts. The procedures must be created by the system administrator in the master
database and executed under the sysadmin fixed server role as a background process. The procedures can't have any input or output parameters. For more information, see Execute a Stored Procedure.
Procedures are nested when one procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. Procedures and managed code references can be nested up to 32 levels. The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. Methods invoked from within the managed code don't count against the nesting level limit. However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.
Attempting to exceed the maximum nesting level causes the entire calling chain to fail. You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.
Interoperability
The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL procedure is created or modified. These original settings are used when the procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.
Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS aren't saved when a procedure is created or modified. If the logic of the 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 procedure, the setting remains in effect only until the procedure has finished running. The setting is then restored to the value the procedure had when it was called. This enables individual clients to set the options they want without affecting the logic of the procedure.
Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. These must be the only statements in the batch. The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.
Note
SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting 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.
Limitations and restrictions
The CREATE PROCEDURE statement can't be combined with other Transact-SQL statements in a single batch.
The following statements can't be used anywhere in the body of a stored procedure.
CREATE | SET | USE |
---|---|---|
CREATE AGGREGATE | SET SHOWPLAN_TEXT | USE database_name |
CREATE DEFAULT | SET SHOWPLAN_XML | |
CREATE RULE | SET PARSEONLY | |
CREATE SCHEMA | SET SHOWPLAN_ALL | |
CREATE or ALTER TRIGGER | ||
CREATE or ALTER FUNCTION | ||
CREATE or ALTER PROCEDURE | ||
CREATE or ALTER VIEW |
A procedure can reference tables that don't yet exist. At creation time, only syntax checking is performed. The procedure isn't compiled until it is executed for the first time. Only during compilation are all objects referenced in the procedure resolved. Therefore, a syntactically correct procedure that references tables that don't exist can be created successfully; however, the procedure fails at execution time if the referenced tables don't exist.
You can't specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. However, you can pass a function as a variable as shown in the following example.
-- Passing the function value as a variable.
DECLARE @CheckDate DATETIME = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
If the procedure makes changes on a remote instance of SQL Server, the changes can't be rolled back. Remote procedures don't take part in transactions.
For the Database Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:
- Be declared as a static method.
- Receive the same number of parameters as the number of parameters of the procedure.
- Use parameter types that are compatible with the data types of the corresponding parameters of the SQL Server procedure. For information about matching SQL Server data types to the .NET Framework data types, see Mapping CLR Parameter Data.
Metadata
The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.
View | Description |
---|---|
sys.sql_modules | Returns the definition of a Transact-SQL procedure. The text of a procedure created with the ENCRYPTION option can't be viewed by using the sys.sql_modules catalog view. |
sys.assembly_modules | Returns information about a CLR procedure. |
sys.parameters | Returns information about the parameters that are defined in a procedure |
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities | Returns the objects that are referenced by a procedure. |
To estimate the size of a compiled procedure, use the following Performance Monitor Counters.
Performance Monitor object name | Performance Monitor Counter name |
---|---|
SQLServer: Plan Cache Object | Cache Hit Ratio |
Cache Pages | |
Cache Object Counts 1 |
1 These counters are available for various categories of cache objects including ad hoc Transact-SQL, prepared Transact-SQL, procedures, triggers, and so on. For more information, see SQL Server, Plan Cache Object.
Permissions
Requires CREATE PROCEDURE
permission in the database and ALTER
permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.
For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES
permission on that assembly.
CREATE PROCEDURE and memory-optimized tables
Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. Native procedures are in most cases the more efficient way. For more information, see Natively Compiled Stored Procedures.
The following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table dbo.Departments
:
CREATE PROCEDURE dbo.usp_add_kitchen @dept_id INT, @kitchen_count INT NOT NULL
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
UPDATE dbo.Departments
SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count
WHERE ID = @dept_id
END;
GO
A procedure created without NATIVE_COMPILATION can't be altered to a natively compiled stored procedure.
For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.
Examples
Category | Featured syntax elements |
---|---|
Basic Syntax | CREATE PROCEDURE |
Passing parameters | @parameter
|
Modifying data by using a stored procedure | UPDATE |
Error Handling | TRY...CATCH |
Obfuscating the procedure definition | WITH ENCRYPTION |
Forcing the Procedure to Recompile | WITH RECOMPILE |
Setting the Security Context | EXECUTE AS |
Basic syntax
Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.
A. Create a Transact-SQL procedure
The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2022 database. This procedure doesn't use any parameters. The example then demonstrates three methods of executing the procedure.
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment;
GO
SELECT * FROM HumanResources.vEmployeeDepartment;
The uspGetEmployees
procedure can be executed in the following ways:
EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;
B. Return more than one result set
The following procedure returns two result sets.
CREATE PROCEDURE dbo.uspMultipleResults
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO
C. Create a CLR stored procedure
The following example creates the GetPhotoFromDB
procedure that references the GetPhotoFromDB
method of the LargeObjectBinary
class in the HandlingLOBUsingCLR
assembly. Before the procedure is created, the HandlingLOBUsingCLR
assembly is registered in the local database.
Applies to: SQL Server 2008 (10.0.x) and later, SQL Database (if using an assembly created from assembly_bits.
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
Pass parameters
Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.
D. Create a procedure with input parameters
The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. This procedure accepts only exact matches for the parameters passed.
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
SET NOCOUNT ON;
SELECT FirstName, LastName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
WHERE FirstName = @FirstName AND LastName = @LastName;
GO
The uspGetEmployees
procedure can be executed in the following ways:
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';
E. Use a procedure with wildcard parameters
The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D
).
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
SET NOCOUNT ON;
SELECT FirstName, LastName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
The uspGetEmployees2
procedure can be executed in many combinations. Only a few possible combinations are shown here.
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%';
F. Use OUTPUT parameters
The following example creates the uspGetList
procedure. This procedure returns a list of products that have prices that don't 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.
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
SET NOCOUNT ON;
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.
G. Use a table-valued parameter
The following example uses a table-valued parameter type to insert multiple rows into a table. The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. The stored procedure uses the values to insert multiple rows into a table.
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2022].[Production].[Location]
([Name]
, [CostRate]
, [Availability]
, [ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks2022].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO
H. Use an OUTPUT cursor parameter
The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.
First, create the procedure that declares and then opens a cursor on the Currency
table:
CREATE PROCEDURE dbo.uspCurrencyCursor
@CurrencyCursor CURSOR VARYING OUTPUT
AS
SET NOCOUNT ON;
SET @CurrencyCursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT CurrencyCode, Name
FROM Sales.Currency;
OPEN @CurrencyCursor;
GO
Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO
Modify data by using a stored procedure
Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.
I. Use UPDATE in a stored procedure
The following example uses an UPDATE statement in a stored procedure. The procedure takes one input parameter, @NewHours
and one output parameter @RowCount
. The @NewHours
parameter value is used in the UPDATE statement to update the column VacationHours
in the table HumanResources.Employee
. The @RowCount
output parameter is used to return the number of rows affected to a local variable. A CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours
. When the employee is paid hourly (SalariedFlag
= 0), VacationHours
is set to the current number of hours plus the value specified in @NewHours
; otherwise, VacationHours
is set to the value specified in @NewHours
.
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT, @Rowcount INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
ELSE @NewHours
END
)
WHERE CurrentFlag = 1;
SET @Rowcount = @@rowcount;
GO
DECLARE @Rowcount INT
EXEC HumanResources.Update_VacationHours 40, @Rowcount OUTPUT
PRINT @Rowcount;
Error handling
Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.
J. Use TRY...CATCH
The following example using the TRY...CATCH construct to return error information caught during the execution of a stored procedure.
CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
-- Delete rows from the child table, WorkOrderRouting, for the specified work order.
DELETE FROM Production.WorkOrderRouting
WHERE WorkOrderID = @WorkOrderID;
-- Delete the rows from the parent table, WorkOrder, for the specified work order.
DELETE FROM Production.WorkOrder
WHERE WorkOrderID = @WorkOrderID;
COMMIT
END TRY
BEGIN CATCH
-- Determine if an error occurred.
IF @@TRANCOUNT > 0
ROLLBACK
-- Return the error information.
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
EXEC Production.uspDeleteWorkOrder 13;
GO
/* Intentionally generate an error by reversing the order in which rows
are deleted from the parent and child tables. This change does not
cause an error when the procedure definition is altered, but produces
an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS
BEGIN TRY
BEGIN TRANSACTION
-- Delete the rows from the parent table, WorkOrder, for the specified work order.
DELETE FROM Production.WorkOrder
WHERE WorkOrderID = @WorkOrderID;
-- Delete rows from the child table, WorkOrderRouting, for the specified work order.
DELETE FROM Production.WorkOrderRouting
WHERE WorkOrderID = @WorkOrderID;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Determine if an error occurred.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
-- Return the error information.
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;
GO
DROP PROCEDURE Production.uspDeleteWorkOrder;
Obfuscate the procedure definition
Examples in this section show how to obfuscate the definition of the stored procedure.
K. Use the WITH ENCRYPTION option
The following example creates the HumanResources.uspEncryptThis
procedure.
Applies to: SQL Server 2008 (10.0.x) and later, and Azure SQL Database.
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
SET NOCOUNT ON;
SELECT BusinessEntityID, JobTitle, NationalIDNumber,
VacationHours, SickLeaveHours
FROM HumanResources.Employee;
GO
The WITH ENCRYPTION
option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.
Run sp_helptext
:
EXEC sp_helptext 'HumanResources.uspEncryptThis';
Here is the result set.
The text for object 'HumanResources.uspEncryptThis' is encrypted.
Directly query the sys.sql_modules
catalog view:
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');
Here is the result set.
definition
--------------------------------
NULL
Note
The system stored procedure sp_helptext
is not supported in Azure Synapse Analytics. Instead, use the sys.sql_modules
object catalog view.
Force the procedure to recompile
Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.
L. Use the WITH RECOMPILE option
The WITH RECOMPILE
clause is helpful when the parameters supplied to the procedure aren't typical, and when a new execution plan shouldn't be cached or stored in memory.
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name VARCHAR(30) = '%'
WITH RECOMPILE
AS
SET NOCOUNT ON;
SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
FROM Purchasing.Vendor AS v
JOIN Purchasing.ProductVendor AS pv
ON v.BusinessEntityID = pv.BusinessEntityID
JOIN Production.Product AS p
ON pv.ProductID = p.ProductID
WHERE v.Name LIKE @Name;
Set the security context
Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.
M. Use the EXECUTE AS clause
The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. In the example, the option CALLER
specifies that the procedure can be executed in the context of the user that calls it.
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON;
SELECT v.Name AS Vendor, p.Name AS 'Product name',
v.CreditRating AS 'Rating',
v.ActiveFlag AS Availability
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor pv
ON v.BusinessEntityID = pv.BusinessEntityID
INNER JOIN Production.Product p
ON pv.ProductID = p.ProductID
ORDER BY v.Name ASC;
GO
N. Create custom permission sets
The following example uses EXECUTE AS to create custom permissions for a database operation. Some operations such as TRUNCATE TABLE, don't have grantable permissions. By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the procedure.
CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
O. Create a stored procedure that runs a SELECT statement
This example shows the basic syntax for creating and running a procedure. When running a batch, CREATE PROCEDURE must be the first statement. For example, to create the following stored procedure in AdventureWorksPDW2022, set the database context first, and then run the CREATE PROCEDURE statement.
-- Uses AdventureWorksDW database
--Run CREATE PROCEDURE as the first statement in a batch.
CREATE PROCEDURE Get10TopResellers
AS
BEGIN
SELECT TOP (10) r.ResellerName, r.AnnualSales
FROM DimReseller AS r
ORDER BY AnnualSales DESC, ResellerName ASC;
END
;
GO
--Show 10 Top Resellers
EXEC Get10TopResellers;
See also
- ALTER PROCEDURE (Transact-SQL)
- Control-of-Flow Language (Transact-SQL)
- Cursors
- Data Types (Transact-SQL)
- DECLARE @local_variable (Transact-SQL)
- DROP PROCEDURE (Transact-SQL)
- EXECUTE (Transact-SQL)
- EXECUTE AS (Transact-SQL)
- Stored Procedures (Database Engine)
- sp_procoption (Transact-SQL)
- sp_recompile (Transact-SQL)
- sys.sql_modules (Transact-SQL)
- sys.parameters (Transact-SQL)
- sys.procedures (Transact-SQL)
- sys.sql_expression_dependencies (Transact-SQL)
- sys.assembly_modules (Transact-SQL)
- sys.numbered_procedures (Transact-SQL)
- sys.numbered_procedure_parameters (Transact-SQL)
- OBJECT_DEFINITION (Transact-SQL)
- Create a Stored Procedure
- Use Table-Valued Parameters (Database Engine)
- sys.dm_sql_referenced_entities (Transact-SQL)
- sys.dm_sql_referencing_entities (Transact-SQL)