Creates a stored procedure (a saved collection of Transact-SQL statements) that can take and return user-supplied parameters.
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).
Stored procedures can also be created to run automatically when Microsoft® SQL Server™ starts.
CREATE PROC[EDURE] procedure_name [;number]
[
{@parameter data_type} [VARYING] [= default] [OUTPUT]
]
[,...n]
[WITH
{
RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
[FOR REPLICATION]
AS
sql_statement [...n]
Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters. Specifying the procedure owner name is optional.
Specify a parameter name using an at sign (@) as the first character. The parameter name must 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 EXECUTE.
Note There is no limit on the maximum number of output parameters that can be of cursor data type.
ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement.
Note During an upgrade, SQL Server uses the encrypted comments stored in syscomments to re-create encrypted procedures.
The maximum size of a stored procedure is 128 MB.
A user-defined stored procedure can be created only in the current database (except for temporary procedures, which are always created in tempdb). The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.
Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs, SQL Server generates an error. To prevent passing a NULL parameter value to a column that does not allow NULLs, add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.
It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in a stored procedure, such as when creating a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way SQL Server assigns the NULL or NOT NULL attributes to columns if they 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 behaviors. If NULL or NOT NULL is explicitly stated for each column, the temporary tables will be created with the same nullability for all connections that execute the stored procedure.
SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. 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 during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the 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 altered. If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients freedom to set the options wanted without affecting the logic of the stored procedure.
Note Whether SQL Server interprets an empty string as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.
To display the text used to create the procedure, execute sp_helptext (in the database in which the procedure exists) with the procedure name as the parameter.
Note Stored procedures created with the ENCRYPTION option cannot be viewed with sp_helptext.
For a report on the objects referenced by a procedure, use sp_depends.
To rename a procedure, use sp_rename.
SQL Server allows the creation of stored procedures that reference objects that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when it is executed, if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references objects which do not exist can be created successfully, but will fail at execution time because referenced objects do not exist. For more information, see Deferred Name Resolution and Compilation.
SQL Server allows Transact-SQL stored procedures to refer to tables that do not exist at creation time. This ability is called deferred name resolution. If, however, the Transact-SQL stored procedure refers to a table defined within the stored procedure, a warning is issued at creation time if the compatibility level setting (set by executing sp_dbcmptlevel) is 65. An error message is returned at execution time if the table referenced does not exist. For more information, see sp_dbcmptlevel and Deferred Name Resolution and Compilation.
When a CREATE PROCEDURE statement is executed successfully, the procedure’s name is stored in the sysobjects system table and the text of the CREATE PROCEDURE statement is stored in syscomments. When executed for the first time, the procedure is compiled to determine an optimal access plan to retrieve the data.
Stored procedures can use the cursor data type only for OUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.
Note The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Because OUTPUT parameters must be bound before an application can execute a stored procedure, stored procedures with cursor OUTPUT parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, stored procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.
The following rules pertain to cursor output parameters when the procedure is executed:
Note An empty result set is not the same thing as a null value.
Note The closed state matters only at return time. For example it is valid to close a cursor part way through the procedure, to open it again later in the procedure, and return that cursor’s result set to the calling batch, stored procedure, or trigger.
SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends.
Temporary procedures named with # and ## can be created by any user. After the procedure is created, the owner of the local procedure is the only one who can use it. Permission to execute a local temporary procedure cannot be granted to other users. If a global temporary procedure is created, all users can access it; permissions cannot be revoked explicitly. Explicitly creating a temporary procedure in tempdb (naming without a number sign) can be performed only by those with explicit CREATE PROCEDURE permission in the tempdb database. Permission can be granted to and revoked from these procedures.
Note Heavy use of temporary stored procedures can create contention on the system tables in tempdb and adversely affect performance. It is recommended that sp_executesql be used instead. sp_executesql does not store data in the system tables and therefore avoids the problem.
One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.
There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you must execute multiple procedures at startup but don’t need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one connection.
Execution of the stored procedures starts when the last database has been recovered at startup. To skip launching these stored procedures, you can specify trace flag 4022 as a startup parameter. If you start SQL Server with minimal configuration (using the -f flag), the startup stored procedures are not executed. For more information, see Trace Flags.
To create a startup stored procedure, you must be logged in as a member of the sysadmin fixed server role and create the stored procedure in the master database.
Use sp_procoption to:
Stored procedures can be nested (one stored procedure calling 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 levels of nesting causes the whole calling procedure chain to fail. The current nesting level is returned by the @@NESTLEVEL function.
To estimate the size of a compiled stored procedure, use these Performance Monitor Counters.
| Performance Monitor object name | Performance Monitor Counter name |
|---|---|
| SQLServer: Buffer Manager | Cache Size (pages) |
| SQLServer: Cache Manager | Cache Hit Ratio |
| Cache Pages | |
| Cache Object Counts* | |
| * These counters are available for various categories of cache objects including adhoc sql, prepared sql, procedures, triggers, and so on. | |
For more information, see SQL Server: Buffer Manager Object and SQL Server: Cache Manager Object.
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch. The SET option chosen remains in effect during the execution of the stored procedure and then reverts to its former setting.
Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
CREATE PROCEDURE permissions default to the members of the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permissions to execute a stored procedure are given to the procedure owner, who can grant execution permission on it to other database users.
This stored procedure returns all authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure does not use any parameters.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
The au_info_all stored procedure can be executed in these ways:
EXECUTE au_info_all
-- Or
EXEC au_info_all
Or, if this procedure is the first statement within the batch:
au_info_all
This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
The au_info stored procedure can be executed in these ways:
EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
Or, if this procedure is the first statement within the batch:
au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'
This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure pattern matches the parameters passed or, if not supplied, uses the preset defaults.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
The au_info2 stored procedure can be executed in many combinations. Only a few combinations are shown here:
EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'
OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statements to access a value set during the procedure execution. In this example, a stored procedure (titles_sum) is created and allows one optional input parameter and one output parameter.
First, create the procedure:
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
Next, use the OUTPUT parameter with control-of-flow language.
Note The OUTPUT variable must be defined during the table creation as well as during use of the variable.
The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @@SUM = variable is used).
DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
Here is the result set:
Title Name
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
(3 row(s) affected)
Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.
First, create the procedure that declares and then opens a cursor on the titles table:
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles
OPEN @titles_cursor
GO
Next, execute 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.
USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure will not be typical and when a new execution plan should not be cached or stored in memory.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_by_author' AND type = 'P')
DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',
title AS Title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO
The WITH ENCRYPTION clause hides the text of a stored procedure from users. This example creates an encrypted procedure and uses the sp_helptext system stored procedure and then attempts to select directly from the syscomments table.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
EXEC sp_helptext encrypt_this
Here is the result set from the encrypt_this stored procedure:
The object's comments have been encrypted.
Next, select the identification number and text of the encrypted stored procedure contents.
SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.name = 'encrypt_this'
Here is the result set:
Note The text column output is shown on a separate line. When executed, this information appears on the same line as the id column information.
id text
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????
(1 row(s) affected)
This example creates a procedure to display all the tables and their corresponding indexes with a table name beginning with the string emp. If not specified, this procedure returns all tables (and indexes) with a table name beginning with sys.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_showindexes' AND type = 'P')
DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
@@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO
USE pubs
EXEC sp_showindexes 'emp%'
GO
Here is the result set:
TABLE_NAME INDEX_NAME INDEX_ID
---------------- ---------------- ----------------
emp_pay employeeID_ind 1
employee employee_ind 1
employee PK_emp_id 2
(3 row(s) affected)
This example shows four procedures and the various ways that deferred name resolution can be used. Each stored procedure is created, although the table or column referenced does not exist at compile time.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc1' AND type = 'P')
DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
SELECT *
FROM does_not_exist
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'P' AND o.name = 'proc1'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc2' AND type = 'P')
DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
DECLARE @middle_init char(1)
SET @middle_init = NULL
SELECT au_id, middle_initial = @middle_init
FROM authors
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'P' and o.name = 'proc2'