Creating Stored Procedures (Database Engine)
You can create stored procedures using the CREATE PROCEDURE Transact-SQL statement.
Before creating a stored procedure, consider that:
CREATE PROCEDURE statements cannot be combined with other SQL statements in a single batch.
To create procedures, you must have CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created. For CLR stored procedures, you must either own the assembly referenced in <method_specifier>, or have REFERENCES permission on that assembly.
Stored procedures are schema-scoped objects, and their names must follow the rules for identifiers.
You can create a stored procedure only in the current database.
When creating a stored procedure, you should specify:
Any input parameters and output parameters to the calling procedure or batch.
The programming statements that perform operations in the database, including calling other procedures.
The status value returned to the calling procedure or batch to indicate success or failure (and the reason for failure).
Any error handling statements needed to catch and handle potential errors.
Error handing functions such as ERROR_LINE and ERROR_PROCEDURE can be specified in the stored procedure. For more information, see Using TRY...CATCH in Transact-SQL.
Naming Stored Procedures
We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures. The name you choose may conflict with some future system procedure. If your application uses nonschema qualified name references and your own procedure name conflicts with a system procedure name, your application will break because the name binds to the system procedure, not your own.
A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead. The following example demonstrates this behavior.
USE AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
SELECT FirstName, LastName FROM Person.Contact;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO
Using an explicit schema qualifier also provides a slight performance advantage. Name resolution is slightly faster if the Database Engine does not have to search multiple schemas to find the procedure. For more information, see Executing a Stored Procedure.
Temporary Stored Procedures
Private and global temporary stored procedures, analogous to temporary tables, can be created with the # and ## prefixes added to the procedure name. # denotes a local temporary stored procedure; ## denotes a global temporary stored procedure. These procedures do not exist after SQL Server is shut down.
Temporary stored procedures are useful when connecting to earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches. Applications connecting to SQL Server 2000 and higher should use the sp_executesql system stored procedure instead of temporary stored procedures. Only the connection that created a local temporary procedure can execute it, and the procedure is automatically deleted when the connection is closed.
Any connection can execute a global temporary stored procedure. A global temporary stored procedure exists until the connection used by the user who created the procedure is closed and any currently executing versions of the procedure by any other connections are completed. Once the connection that was used to create the procedure is closed, no further execution of the global temporary stored procedure is allowed. Only those connections that have already started executing the stored procedure are allowed to complete.
If a stored procedure not prefixed with # or ## is created directly in the tempdb database, the stored procedure is automatically deleted when SQL Server is shut down because tempdb is re-created every time SQL Server is started. Procedures created directly in tempdb exist even after the creating connection is terminated.
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.
CLR stored procedures cannot be created as temporary stored procedures.
Examples
A. Using a simple procedure with a complex SELECT
The following stored procedure returns all employees (first and last names supplied), their titles, and their department names from a view. This stored procedure does not use any parameters.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, Department
FROM HumanResources.vEmployeeDepartmentHistory;
GO
The uspGetEmployees stored procedure can be executed in these ways:
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
The following stored procedure returns only the specified employee (first and last name supplied), her title, and her department name from a view. This stored procedure accepts exact matches for the parameters passed.
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
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName;
GO
The uspGetEmployees stored 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';
C. Using a simple procedure with wildcard parameters
The following stored procedure returns only the specified employees (first and last names supplied), their titles, and their departments from a view. This stored procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D).
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
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO
The uspGetEmployees2 stored procedure can be executed in many combinations. Only a few 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%';
D. Using OUTPUT parameters
The following example creates the uspGetList stored procedure, which 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 allow 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
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 during the procedure creation as well as during the 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 @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.
See Also