创建存储过程（数据库引擎）

项目
12/15/2008

您可以使用 Transact-SQL 语句 CREATE PROCEDURE 来创建存储过程。

创建存储过程前，请考虑下列事项：

CREATE PROCEDURE 语句不能与其他 SQL 语句在单个批处理中组合使用。
要创建过程，您必须具有数据库的 CREATE PROCEDURE 权限，还必须具有对架构（在其下创建过程）的 ALTER 权限。对于 CLR 存储过程，您必须拥有在 <method_specifier> 中引用程序集或拥有对该程序集的 REFERENCES 权限。
存储过程是架构作用域内的对象，它们的名称必须遵守标识符规则。
您只能在当前数据库中创建存储过程。

创建存储过程时，应指定：

所有输入参数和向调用过程或批处理返回的输出参数。
执行数据库操作（包括调用其他过程）的编程语句。
返回至调用过程或批处理以表明成功或失败（以及失败原因）的状态值。
捕获和处理潜在的错误所需的任何错误处理语句。
Microsoft SQL Server 2005 引入了新的错误处理函数，如 ERROR_LINE 和 ERROR_PROCEDURE。您可以在存储过程中指定它们。有关详细信息，请参阅在 Transact-SQL 中使用 TRY...CATCH。

命名存储过程

建议您不要创建任何使用 sp_ 作为前缀的存储过程。SQL Server 使用 sp_ 前缀指定系统存储过程。您选择的名称可能会与以后的某些系统过程发生冲突。如果应用程序引用了不符合架构的名称，而您自己的过程名称与系统过程名称相冲突，则该名称将绑定到系统过程而非您自己的过程，这将导致应用程序中断。

如果用户定义存储过程与系统存储过程名称相同，而且不合法或者符合 dbo 架构，则该存储过程将永不执行，取而代之的是始终执行系统存储过程。下面的示例对此行为进行了演示。

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

使用显式架构限定符也能稍微改善性能。如果数据库引擎不必搜索多个架构来查找过程，则名称解析会稍微快一点。有关详细信息，请参阅执行存储过程。

临时存储过程

专用和全局临时存储过程与临时表类似，都可以通过向该过程名称添加 # 和 ## 前缀的方法进行创建。# 表示本地临时存储过程，## 表示全局临时存储过程。SQL Server 关闭后，这些过程将不复存在。

SQL Server 的早期版本不支持重用 Transact-SQL 语句或批处理的执行计划，当连接到这些早期版本时，临时存储过程很有用。当连接到 SQL Server 2000 或更高版本的应用程序时，应使用 sp_executesql 系统存储过程，而非临时存储过程。只有创建了本地临时过程的连接才可以执行它。在连接关闭之后，临时过程将被自动删除。

任何连接都可执行全局临时存储过程。只有创建该过程的用户所用的连接关闭，并且所有其他连接所用的该过程的当前执行版本运行完毕后，全局临时存储过程才不再存在。一旦用于创建该过程的连接关闭，将不再允许启动执行该全局临时存储过程。只允许那些已启动执行该存储过程的连接完成该过程的运行。

由于 tempdb 会在每次 SQL Server 启动时重新创建，因而，如果在 tempdb 数据库中直接创建了一个不以 # 或 ## 为前缀的存储过程，则当 SQL Server 关闭时，存储过程将被自动删除。在 tempdb 中直接创建的过程在创建它的连接终止后仍然存在。

注意：
过多使用临时存储过程可能会产生对 tempdb 中的系统表的争用，反而会降低性能。建议改为使用 sp_executesql。sp_executesql 不在系统表中存储数据，因而可以避免此问题。

不能将 CLR 存储过程创建为临时存储过程。

示例

A. 使用带有复杂 SELECT 的简单过程

下面的存储过程返回某个视图中的所有雇员（提供了姓名）、他们的职务和部门名称。该存储过程不使用任何参数。

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

uspGetEmployees 存储过程可通过下列方式执行：

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

B. 使用带有参数的简单过程

下面的存储过程只从视图中返回指定的雇员（提供名字和姓氏）及其职务和部门名称。该存储过程接受与传递的参数精确匹配的值。

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

uspGetEmployees 存储过程可通过下列方式执行：

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. 使用带有通配符参数的简单过程

下面的存储过程只从视图中返回指定的一些雇员（提供名字和姓氏）及其职务和部门名称。该存储过程对传递的参数进行模式匹配。如果没有提供参数，则使用预设的默认值（姓氏以字母 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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

uspGetEmployees2 存储过程可通过多种组合方式执行。下面只列出了部分组合：

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. 使用 OUTPUT 参数

以下是创建 uspGetList 存储过程的示例，它返回其价格不超过指定金额的产品列表。此示例显示如何使用多个 SELECT 语句和多个 OUTPUT 参数。使用 OUTPUT 参数，外部过程、批或多个 Transact-SQL 语句可以访问在过程执行期间设置的值。

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

执行 uspGetList 可以返回成本低于 $700 的 Adventure Works 产品（自行车）列表。OUTPUT 参数 @Cost 和 @ComparePrices 与控制流语言一起使用，以便在**“消息”**窗口中返回消息。

注意：
OUTPUT 变量必须在过程创建和变量使用期间进行定义。参数名和变量名不一定要匹配；但是，数据类型和参数位置必须匹配（除非使用 @ListPrice= variable 的形式）。

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)))+'.'

下面是部分结果集：

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.

请参阅

通过