存储过程是一段可执行的服务端程序。如果使用得当，它可以为你提供强大的功能和性能优势。不同于用户定义函数（UDF），存储过程允许有副作用（side effect）。也就是说，它可以修改表中的数据，甚至可以修改对象的架构。存储过程可以用作安全层(security layer)。通过授予对存储过程的执行权限而不是授予对基对象的执行权限，你可以控制对象的访问。你可以在存储过程中执行输入验证，通过使用存储过程，你可以允许那些只有作为一个整体执行才会有意义的操作，而不是允许用户直接操作对象。

你也可以利用存储过程实现封装（encapsulation），创建了存储过程之后，如果你找到一种更高效的方法完成某项任务，只需执行ALTER PROCEDURE语句修改存储过程的实现即可。只要存储过程的接口不变，用户和应用程序就不会受到影响。但是如果你在客户端实现业务逻辑，要修改该逻辑将非常痛苦。

存储过程在性能方面也极具优势。默认情况下，存储过程将重用已缓存的执行计划，节省了分析、解析和优化代码所需的CPU资源和时间。因为缩短了客户端提交到Microsoft SQL Server的代码字符串，网络流量被降到最低。客户端只需要提交存储过程的名称和参数，而不是全部的代码。此外，所有的操作都在服务器端完成，避免了在客户端和服务器端之间的多次往返。存储过程只需把最终结果通过网络传输到客户端。

本章将介绍存储过程。首先简要介绍SQL Server 2005所支持的不同类型的存储过程，然后再深入研究它的细节。本章的内容包括存储过程的接口、解析过程（resolution process）、编译、重新编译和执行计划重用、EXECUTE AS 子句以及新的公共语言运行时（CLR）存储过程。这一章你将有两次机会练习所学到的知识，开发用于解决一些常见问题的存储过程。

3.1  存储过程的种类

SQL Server2005支持不同类型的存储过程：用户定义存储过程、系统存储过程和扩展存储过程。你可以使用T-SQL或CLR开发用户定义存储过程。这一节将简要介绍这些存储过程。

用户定义存储过程

用户定义存储过程在用户数据库中创建，通常与数据库对象进行交互。要调用一个用户定义存储过程，指定EXEC（或EXECUTE）命令和限定架构的存储过程名称以及参数：

EXEC dbo.usp_Proc1 <arguments>;

作为一个示例，运行代码清单7-1中的代码，在Northwind数据库中创建usp_GetSortedShippers存储过程。

代码清单7-1  创建usp_GetSortedShippers的脚本

USE Northwind;

GO

IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL

  DROP PROC dbo.usp_GetSortedShippers;

GO

-- 存储过程usp_GetSortedShippers返回按请求列排序的发货人

CREATE PROC dbo.usp_GetSortedShippers

  @colname AS sysname = NULL

AS

DECLARE @msg AS NVARCHAR(500);

-- 验证输入

IF @colname IS NULL

BEGIN

  SET @msg = N'A value must be supplied for parameter @colname.';

  RAISERROR(@msg, 16, 1);

  RETURN;

END IF @colname NOT IN(N'ShipperID', N'CompanyName', N'Phone')

BEGIN

  SET @msg =

    N'Valid values for @colname are: '

    + N'N''ShipperID'', N''CompanyName'', N''Phone''.';

  RAISERROR(@msg, 16, 1);

  RETURN;

END

-- 返回按请求列排序的发货人

IF @colname = N'ShipperID'

  SELECT ShipperID, CompanyName, Phone

  FROM dbo.Shippers

  ORDER BY ShipperID;

ELSE IF @colname = N'CompanyName'

  SELECT ShipperID, CompanyName, Phone

  FROM dbo.Shippers

  ORDER BY CompanyName;

ELSE IF @colname = N'Phone'

  SELECT ShipperID, CompanyName, Phone

  FROM dbo.Shippers

  ORDER BY Phone;

GO

这个存储过程接收Northwind数据库中Shippers表的列名称作为输入，验证输入完成后，返回Shippers表的按指定列排序的行。验证输入包括检查是否指定了列名称以及Shippers表是否包含指定的列名称。在本章的后面，我将更深入地讨论参数化排序这一主题，现在我只是提供一个用户定义存储过程的简单示例。运行下面的代码调用usp_GetSortedShippers，并用N'CompanyName'作为输入，产生的输出如表7-1所示。

USE Northwind;

EXEC dbo.usp_GetSortedShippers @colname = N'CompanyName';

表7-1  按 CompanyName 排序的Shippers

ShipperID	CompanyName	Phone
3	Federal Shipping	(503) 555-9931
1	Speedy Express	(503) 555-9831
2	United Package	(503) 555-3199

如果存储过程是批处理中的第一条语句，你可以省略EXEC关键字，但我建议你一直使用它。你可以忽略存储过程的架构名称（本例中是dbo），但如果你忽略它，SQL Server必须能够解析该架构。SQL Server2005按下面的顺序进行解析（摘自联机丛书）。

n  当前数据库的 sys 架构。

n  调用方的默认架构（如果在批处理或动态 SQL 中执行）。或者，如果非限定的过程名称出现在另一个过程定义的主体中，则继续搜索包含这一过程的架构。

n  当前数据库中的 dbo 架构。

例如，假设你连接到Northwind数据库，你的用户在Northwind中的默认架构是schema1。在批处理调用下面的代码：

EXEC usp_GetSortedShippers @colname = N'CompanyName';

解析过程按下面的顺序进行：

n  在Northwind中的sys架构中查询usp_GetSortedShippers（sys.usp_GetSortedShippers）。如果找到则执行它；如果没找到，继续第二步（就像该示例一样）。

n  如果是在批处理中（当前示例就是如此）或动态SQL中调用的，在schema1中查找usp_GetSortedShippers（schema1.usp_GetSortedShippers）。如果是在另一个存储过程中调用的（比如是schema2.usp_AnotherProc），在schema2中继续查找usp_GetSortedShippers。如果找到则执行它；如果没找到则继续下一步（就像该示例一样）。

n  在dbo架构（dbo.usp_GetSortedShippers）中查找usp_GetSortedShippers。如果找到（就像该示例一样）则执行它，如果没找到则生成一个解析错误。

除了未指定架构会导致混淆和意义不明确的情况外，每次都指定架构还有另外一个重要原因。当很多连接同时运行同一个存储过程时，如果未指定架构名称，这些连接可能会因为要获取编译锁（compile lock）而互相阻塞。

我前面曾提到过，存储过程可以用作安全层。你可以通过授予执行存储过程的权限，而不是授予对基对象的权限来控制对象访问。例如，假设在Northwind数据库中有一个数据库用户user1。你想允许user1调用usp_GetSortedShippers存储过程，但你又不想让用户直接访问Shippers表。为此，你可以授予该用户对这个存储过程的EXECUTE权限并拒绝对Shippers表的SELECT权限（或者其他权限），就像这样：

DENY SELECT ON dbo.Shippers TO user1;

GRANT EXECUTE ON dbo.usp_GetSortedShippers TO user1;

SQL Server允许user1执行该存储过程。但当user1尝试直接查询Shippers表时：

SELECT ShipperID, CompanyName, Phone

FROM dbo.Shippers;

SQL Server将生成下面的错误：

Msg 229, Level 14, State 5, Line 1

SELECT permission denied on object 'Shippers', database 'Northwind', schema 'dbo'.

利用这种安全模型，你可以对那些允许用户执行的操作进行更高级的控制。

通过usp_GetSortedShippers这个示例存储过程，我还要指出其他几个存储过程编程方面的特点：

n  注意我在查询中显式地指定了列名称，没有使用SELECT * 。使用SELECT　*不是一个好习惯。该表的架构可能发生变化，这种变化会导致你的应用程序崩溃。此外，如果你确实只需要表中的部分列而不是所有列，使用SELECT*会阻止优化器利用定义在该子集上的索引。

n  查询缺少筛选器。这本身并不是什么坏习惯，如果你确实需要表中的所有行，只能这样做。但当你在Solid Quality Learning 学习性能调整项目之后，你会大吃一惊，我们见过这样的产品应用程序，它需要筛选后的数据，但却只在客户端进行筛选。这种方法对SQL Server和网络都会带来极大的压力。筛选器会让优化器考虑使用索引，而使用索引会最小化I/O成本。同样，在服务器端筛选数据还可以减少网络流量。如果你需要筛选后的数据，请确保在服务器端使用WHERE子句（或相关的ON、HAVING）进行筛选!

n  使用分号（；）结束语句。尽管分号并不是T-SQL所必须的，但ANSI要求这样做。在SQL Server2000中，根本不需要分号，它是可选的。在SQL Server2005中，有些语句需要用分号结束以避免代码意义不明确。例如，WITH关键字有不同的用途，定义CTE、指定表提示等。SQL Server要求为CTE的WITH子句前面的语句添加后缀以避免歧义。习惯用分号为所有语句添加后缀是一个好习惯。

现在让我们回到本节的内容，用户定义存储过程。

我前面提到过，要调用用户定义存储过程，需要指定EXEC、限定架构的存储过程名称以及参数值（如果有参数的话）。如果存储过程中的系统对象和用户对象名称引用

不是完全限定的（也就是说，没有数据库前缀），会在创建该存储过程的数据库中被解析。如果你想调用位于另一个数据库中的用户定义存储过程，必须限定数据库名称。例如，如果你连接到db1数据库并调用存储过程usp_Proc1，而它位于db2中，你应该使用下面的代码。

USE db1;

EXEC db2.dbo.usp_Proc1 <arguments>;

调用另一个数据库中的存储过程不会改变一个事实，即，未完全限定的对象名在创建该存储过程的数据库中被解析（本示例中是db2）。

如果你想调用位于另一个SQL Server实例的远程存储过程，你应该使用完全限定的存储过程名称，包括链接服务器名称：server.database.schema.proc。

完成后，运行下面的代码进行清理。

USE Northwind;

GO

IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL

  DROP PROC dbo.usp_GetSortedShippers;

特殊存储过程

我使用“特殊存储过程“的意思是说在master数据库中以sp_为前缀创建的存储过程。以该方式创建的存储有着特殊的行为。

作为一个示例，下面的代码创建特殊存储过程sp_Proc1，它打印数据库上下文并查询INFORMATION_SCHEMA.TABLES，先使用动态SQL，然后使用静态查询。

SET NOCOUNT ON;

USE master;

GO

IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL

  DROP PROC dbo.sp_Proc1;

GO

CREATE PROC dbo.sp_Proc1

AS

PRINT 'master.dbo.sp_Proc1 executing in ' + DB_NAME();

-- 动态查询

EXEC('SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ''BASE TABLE'';');

-- 静态查询

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE';

GO

特殊存储过程所独有的特点之一是当你连接到另一个数据库时不需要为其名称限定数据库。例如，你连接到Northwind后不用为其名称限定数据库就可以运行：

USE Northwind;

EXEC dbo.sp_Proc1;

PRINT命令返回‘master.dbo.sp_Proc1 executing in Northwind’。打印的消息中的数据库名称是通过DB_NAME函数获取的。打印结果表明DB_NAME认为当前数据库是Northwind而不是master。同样，动态SQL也假定在当前数据库的上下文中。所以EXEC命令（查询INFORMATION_SCHEMA.TABLES）从Northwind数据库返回表名称。相对于前面的两条语句，对INFORMATION_SCHEMA.TABLES执行的静态查询好像会认为它运行在master中，它从master数据库而不是Northwind返回表名称。同样，如果你使用静态代码引用用户对象（例如，T1表），SQL Server将在master中查找。在SQL Server 2000中，引用系统表（如sysobjects）的静态代码在当前数据库解析。SQL Server 2005保留了这种行为，但它不是通过使用新的目录视图（catalog view）（如sys.objects）实现的，而是使用向后兼容的视图（如sys.sysobjects）实现的。

有意思的是，除了存储过程，sp_前缀与其他对象类型结合时的行为也不可思议。

例如，下面的代码在master创建一个以sp_为前缀的表。

USE master;

GO

IF OBJECT_ID('dbo.sp_Globals') IS NOT NULL

  DROP TABLE dbo.sp_Globals;

GO

CREATE TABLE dbo.sp_Globals

(

  var_name sysname     NOT NULL PRIMARY KEY,

  val      SQL_VARIANT NULL

);

下面的代码在不同的数据库上下文之间切换，即使没有为表名称限定数据库名称，它总是可以找到该表。

USE Northwind;

INSERT INTO dbo.sp_Globals(var_name, val)

  VALUES('var1', 10);

USE pubs;

INSERT INTO dbo.sp_Globals(var_name, val)

  VALUES('var2', CAST(1 AS BIT));

USE tempdb;

SELECT var_name, val FROM dbo.sp_Globals;

最后一个查询生成的输出如表7-2所示

表7-2  sp_Globals表的内容

var_name	Val
var1	10
var2	1

运行下面的代码进行清理：

USE master;

GO

IF OBJECT_ID('dbo.sp_Globals') IS NOT NULL

  DROP TABLE dbo.sp_Globals;

先不要删除sp_Proc1，下一节还会用到它。

系统存储过程

系统存储过程是微软内置在产品中的存储过程。在SQL Server 2000中，系统存储过程位于master数据库，以sp_为前缀，并标记为“system”（MS Shipped）。在SQL Server 2005中，系统存储过程在物理上位于一个内部隐藏的资源数据库，逻辑上存在于每个数据库中。

特殊存储过程（使用sp_前缀，在master中创建）也被标记为系统存储过程，并具备另外一些独特的行为。当SQL Server的安装程序运行安装脚本以创建系统存储过程时，安装脚本使用未公开的存储过程sp_MS_marksystemobject把这些存储过程标记为系统存储过程。

运行下面的代码把sp_Proc1存储过程标记为系统存储过程：

USE master;

EXEC sp_MS_marksystemobject 'dbo.sp_Proc1';

如果你再在master之外的数据库中运行sp_Proc1，你会发现该存储过程内的所有代码都假定是在当前数据库的上下文中。

USE Northwind;

EXEC dbo.sp_Proc1;

USE pubs;

EXEC dbo.sp_Proc1;

EXEC Northwind.dbo.sp_Proc1;

实践中，要避免为用户定义存储过程使用sp_前缀。如果本地数据库包含一个与master中的存储过程的名称和架构都相同的存储过程，用户定义存储过程将被调用。为演示这一点，在Northwind数据库中再创建一个名为sp_Proc1的存储过程。

USE Northwind;

GO

IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL

  DROP PROC dbo.sp_Proc1;

GO

CREATE PROC dbo.sp_Proc1

AS

PRINT 'Northwind.dbo.sp_Proc1 executing in ' + DB_NAME();

GO

如果你运行下面的代码，你会看到当连接到Northwind数据库时，将调用Northwind中的sp_Proc1。

USE Northwind;

EXEC dbo.sp_Proc1;

USE pubs;

EXEC dbo.sp_Proc1;

删除Northwind中的版本，因为它会影响下面的示例。

USE Northwind;

GO

IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL

  DROP PROC dbo.sp_Proc1;

有意思的是，系统存储过程还有另外一个独特的行为。它们也在当前数据库中解析系统对象，而且还在当前数据中解析用户对象。为演示这一点，运行下面的代码重新创建sp_Proc1存储过程，在存储过程中查询用户表Orders，然后把该存储过程标记为系统存储过程。

USE master;

GO

IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL

  DROP PROC dbo.sp_Proc1;

GO

CREATE PROC dbo.sp_Proc1

AS

PRINT 'master.dbo.sp_Proc1 executing in ' + DB_NAME();

SELECT OrderID FROM dbo.Orders;

GO

EXEC sp_MS_marksystemobject 'dbo.sp_Proc1';

运行Northwind中的sp_Proc1，你会发现对Northwind中Orders的查询可以正确运行：

USE Northwind;

EXEC dbo.sp_Proc1;

在pubs中进行类似的尝试：

USE pubs;

EXEC dbo.sp_Proc1;

master.dbo.sp_Proc1 executing in pubs

Msg 208, Level 16, State 1, Procedure sp_Proc1, Line 5

Invalid object name 'dbo.Orders'.

该错误表明SQL Server在pubs中查找Orders表未果。

完成后，运行下面的代码进行清理。

USE master;

GO

IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL

  DROP PROC dbo.sp_Proc1;

GO

USE Northwind

GO

IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL

  DROP PROC dbo.sp_Proc1;

其他类型的存储过程

SQL Server还支持其他一些类型的存储过程：

n  临时存储过程　通过在名称前面增加一个或两个井号（#或##）可以创建临时存储过程。使用一个井号表示该存储过程是一个局部临时存储过程，两个井号表示全局临时存储过程。局部临时存储过程和全局临时存储过程在可见性

（visibility）及范围（scope）方面类似于局部临时表和全局临时表。

n  扩展存储过程　该类型的存储过程允许使用C这样的编程语言利用开放数据服务（Open Data Services ，ODS）API创建外部程序。它们在SQL Server的早期版本中用于扩展产品的功能。先使用ODS API编写扩展程序，然后编译成 .dll文件，再在SQL Server中注册为扩展存储过程。它们与使用T-SQL编写的用户定义存储过程相似。SQL Server 2005支持扩展存储过程只是为了向后兼容，在以后的SQL Server版本中将不再支持。现在你可以利用 .NET集成开发CLR存储过程，以及其他类型的程序。我将在本章的后面介绍CLR存储过程。