3.2  存储过程接口

这一节介绍存储过程的接口（即输入和输出参数）。

输入参数

你可以在存储过程头定义输入参数。当调用存储过程时，必须为输入参数提供值，除非使用默认值。作为一个示例，下面的代码创建存储过程usp_GetCustOrders，它接受一个消费者ID和一个时间范围作为输入，并返回该消费者在指定时间范围内的订单。

USE Northwind;

GO

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

  DROP PROC dbo.usp_GetCustOrders;

GO

CREATE PROC dbo.usp_GetCustOrders

  @custid   AS NCHAR(5),

  @fromdate AS DATETIME = '19000101',

  @todate   AS DATETIME = '99991231'

AS

SET NOCOUNT ON;

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders

WHERE CustomerID = @custid

  AND OrderDate >= @fromdate

  AND OrderDate < @todate;

GO

当调用存储过程时，你必须为那些在定义中未提供默认值的输入参数指定输入值（本例中是＠custid）。调用存储过程时有两种为参数赋值的格式：非命名格式（unnamed）和命名格式（named）。在非命名参数的格式中，不需要指定参数名，只指定参数值即可。但必须按参数声明的顺序指定输入。你可以忽略那些包含默认值而且位于参数列表末尾的参数。但不能忽略已经为其提供值的两个参数之间的参数。如果想让参数使用默认值，你需要为该参数指定DEFAULT关键字。

作为一个示例，下面的代码调用该存储过程，但没有为最后两个参数指定输入，而是使用它们的默认值，产生的输出如表7-3所示。

EXEC dbo.usp_GetCustOrders N'ALFKI';

表 7-3  消费者 ALFKI的订单

OrderID	CustomerID	EmployeeID	OrderDate
10643	ALFKI	6	1997-08-25 00:00:00.000
10692	ALFKI	4	1997-10-03 00:00:00.000
10702	ALFKI	4	1997-10-13 00:00:00.000
10835	ALFKI	1	1998-01-15 00:00:00.000
10952	ALFKI	1	1998-03-16 00:00:00.000
11011	ALFKI	3	1998-04-09 00:00:00.000

如果你想为第三个参数指定一个值，但让第二个参数使用默认值，应该为第二个参数指定DEFAULT关键字。

EXEC dbo.usp_GetCustOrders N'ALFKI', DEFAULT, '20060212';

该代码也会产生如表7-3所示的输出。

当然，如果你想为所有参数指定值，按顺序指定这些值即可，就像这样：

EXEC dbo.usp_GetCustOrders N'ALFKI', '19970101', '19980101';

该语句会产生如表7-4所示的输出。

表7-4  消费者 ALFKI在1997年的订单

OrderID	CustomerID	EmployeeID	OrderDate
10643	ALFKI	6	1997-08-25 00:00:00.000
10692	ALFKI	4	1997-10-03 00:00:00.000
10702	ALFKI	4	1997-10-13 00:00:00.000

这些都是存储过程的基础知识。你可能已经很熟悉它们了，但我还是决定先介绍这些基础知识，然后引出推荐的实践。使用非命名赋值格式会导致很多维护方面的问题。如，必须按顺序指定参数，不能忽略可选参数，只观察代码，无法弄清输入的意义以及对应哪个参数。因此，使用命名赋值格式是一个很好的习惯，这种格式要求你指定参数的名称并为其赋值，就像这样：

EXEC dbo.usp_GetCustOrders

  @custid   = N'ALFKI',

  @fromdate = '19970101',

  @todate   = '19980101';

该代码更具可读性。你可以按任意顺序指定输入，如果参数有默认值你可以忽略该参数。

输出参数

输出参数允许你从存储过程返回输出值。在存储过程中更改输出参数会反映到调用方为该输出参数赋值的变量。这个概念与C中的指针或Visual Basic中的ByRef参数类似。

作为一个示例，下面的代码修改usp_GetCustOrders存储过程的定义，为其添加输出参数@ numrows。

ALTER PROC dbo.usp_GetCustOrders

  @custid   AS NCHAR(5),

  @fromdate AS DATETIME = '19000101',

  @todate   AS DATETIME = '99991231',

  @numrows  AS INT OUTPUT

AS

SET NOCOUNT ON;

DECLARE @err AS INT;

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders

WHERE CustomerID = @custid

  AND OrderDate >= @fromdate

  AND OrderDate < @todate;

SELECT @numrows = @@rowcount, @err = @@error;

RETURN @err;

GO

@numrows将返回查询所影响的行数。该存储过程在执行查询后使用RETURN子句返回 @@error函数的值。

要在调用存储过程时得到它返回的输出参数，你需要用调用批处理中的变量为输出参数赋值并指定关键字OUTPUT。要得到返回状态（return statu），你还需要在调用批处理中的存储过程名称和等号前面提供一个变量。下面是一个示例。

DECLARE @myerr AS INT, @mynumrows AS INT;

EXEC @myerr = dbo.usp_GetCustOrders

  @custid   = N'ALFKI',

  @fromdate = '19970101',

  @todate   = '19980101',

  @numrows  = @mynumrows OUTPUT;

SELECT @myerr AS err, @mynumrows AS rc;

存储过程返回的输出如表7-4所示，除此之外它还把返回状态0赋给 @myerr，把受影响的行数（这个例子中是3）赋给 @mynumrows变量。

如果你想用T-SQL处理该存储过程返回的行集，你需要先创建一个表，然后使用INSERT/EXEC语法，如代码清单7-2所示。

代码清单7-2  把usp_GetCustOrders的输出发送到表

IF OBJECT_ID('tempdb..#CustOrders') IS NOT NULL

  DROP TABLE #CustOrders;

GO

CREATE TABLE #CustOrders

(

  OrderID    INT      NOT NULL PRIMARY KEY,

  CustomerID NCHAR(5) NOT NULL,

  EmployeeID INT      NOT NULL,

  OrderDate  DATETIME NOT NULL

);

DECLARE @myerr AS INT, @mynumrows AS INT;

INSERT INTO #CustOrders(OrderID, CustomerID, EmployeeID, OrderDate)

  EXEC @myerr = dbo.usp_GetCustOrders

    @custid   = N'ALFKI',

    @fromdate = '19970101',

    @todate   = '19980101',

    @numrows  = @mynumrows OUTPUT; SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM #CustOrders;

SELECT @myerr AS err, @mynumrows AS rc;

GO

客户端将把存储过程的输出保存到客户端对象。例如，使用ADO编程时你在Parameters集合中定义输入参数、输出参数和返回状态。如果在存储过程内调用多个查询，将返回多个结果集。在客户端的代码中，你会逐个提取这些结果集。例如，在ADO中使用Recordset对象的NextRecordset属性。存储过程也可以生成其他类型的输出，包括PRINT和RAISERROR命令的输出。它们都由客户端通过客户端接口结构接收。例如，ADO中的Errors集合。

ADO.NET允许你在客户端接收任何可能的SQL Server存储过程的输出。当然，为了接收一些输出，你必须先执行存储过程。使用SqlCommand对象可以执行存储过程，要执行存储过程，必须把SqlCommand对象的CommandType属性设置为CommandType. StoredProcedure。要定义执行哪个存储过程，你必须把存储过程的名称赋值给CommandText属性。根据存储过程的不同类型的输出，使用SqlCommand对象的ExecuteScalar、ExecuteNonQuery、ExecuteReader或 ExecuteXmlReader方法执行存储过程。下面是不同类型的存储过程输出：

n  单行集　在保持连接的情况下，使用SqlDataReader类型的对象接收单行集（这意味着应用程序维护到SQL Server的持久连接。即，连接总是可用的）。如果你想在断开连接的情况下填充DataTable类型的对象（断开连接表示把数据读到DataTable后，应用程序可以断开到SQL Server的连接，然后处理DataTable中的数据，DataTable对象是DataSet类型的对象的成员之一），可以由SqlDataAdapter类型的对象接收。

n  多行集　 使用SqlDataReader类接收。使用数据读取器对象的NextResult方法遍历存储过程返回的所有行集。

n  输出参数　使用SqlCommand对象的Parameters集合接收输出参数。ADO.NET中的SqlParameter对象可以有四种方向：Input、Output、InputOutput 和 ReturnValue。当然，一个参数同时只能有一个方向。关于ReturnValue方向，请参考下一段内容。输入参数仅用于输入，输出参数仅用于输出。SQL Server存储过程输出参数实际上是输入/输出参数，所以当执行存储过程时你可以通过输出参数传递值。因此，你可以在ADO.NET中为SqlParameter对象指定InputOutput方向，但必须在执行存储过程之前为它赋输入值，否则将导致编译错误。

n  返回值　由方向为ReturnValue 的SqlParameter对象接收。返回值参数必须是SqlCommand对象的Parameters集合的第一个参数。

n  受影响行数　接收该值有点复杂。不能依赖SQL Server输出，因为开发人员在存储过程中添加了SET NOCOUNT ON语句。SqlDataReader对象包含RecordsAffected属性，该属性用于获取更新、插入或删除的行数。如果是SELECT语句，不能使用该属性。使用INSERT、UPDATE和DELETE语句时也有一个问题：RecordAffected属性只能获取存储过程中所有DML语句影响的行数。如果你需要分别得到每条DML语句影响的行数，可以在存储过程为每个DML语句定义一个输出参数，然后在每个DML语句之后把 @@rowcount的值保存到输出参数。使用这个方法你也可以很容易的得到SELECT语句影响的行数。

n  错误　所有的 .NET代码都应该对可能出错的操作使用Try‥Catch 块。在Catch块中，你可以捕获真正的错误。即，严重级别大于10的错误，它们表示非常严重的错误，而不仅仅是警告或提示信息。类似于使用SqlCommand对象执行存储过程，在try块中运行可能产生错误的语句。当在Try块中发生错误时，应用程序的控制权立刻转移到Catch块中，在这里你可以访问描述该错误的SqlException对象，SqlException对象包含一个Errors集合。在这个集合中，你可以获取SqlError类型的对象，一个对象表示一个由SQL Server抛出的严重程序从11到16的错误。你可以遍历该集合并读取SQL Server返回的所有错误。SqlError的Number属性保存错误号，Message属性表示错误消息。

n  警告　接收该值也有点麻烦。SQL Server中的警告是严重级别小于等于10的错误消息。如果代码中没有真正的错误，你可以通过处理SqlConnection对象的InfoMessage事件获取存储过程中的警告。InfoMessage事件接收一个SqlInfoMessageEventArgs对象。SqlInfoMessageEventArgs包含一个Errors集合，与前面提到的SQLException对象的Errors集合相似，SQLException对象的Errors集合是一个SqlError类型的集合，而SqlInfoMessageEventArgs中的Errors集合包含严重级别小于等于10的SQL Server错误。你可以遍历该集合并获取所有的SQL Server警告信息。如果存储过程发生真正的错误，你也可以在Catch块中捕获所有的警告和错误，而InfoMessage事件则不再发生。

n  T-SQL PRINT 语句输出　可以用处理警告的方法处理这类输出。利用SqlConnection的InfoMessage事件处理程序（event handler）读取输出，或者如果存储过程中发生真正的错误则在Catch块中读取输出。

n  DBCC 语句输出　一些DBCC命令支持TABLERESULTS选项。如果你使用了该选项，可以使用SqlDataReader对象读取该输出，就像读取行集一样。如果DBCC语句的输出不是表格形式而是文本形式的，你可以使用SqlConnection对象的InfoMessage事件得到这些输出。同样，用法与警告和PRINT输出相同。

n  XML 输出　ADO.NET2.0完全支持新的XML数据类型，所以你可以使用SqlDataReader对象获取表格形式的结果，其中包含XML数据类型的列。使用了FOR　XML子句的SELECT语句的XML输出可以被接收到XmlReader对象，当然，你必须使用SqlCommand对象的ExecuteXmlReader方法。

n  用户定义数据类型 （UDTs）　ADO.NET完全支持UDT，所以你可以用提取本机类型列的值的方法获取UDT列的值。需要注意的是SQL Server只发送值，不会发送UDT代码。因此，要在客户端使用任何的UDT方法，UDT的代码必须在客户端也可用。

n  利用SqlDataReader获取的行集架构　ADO.NET2.0中的SqlDataReader包含一个新的方法GetSchemaTable。该方法可以用于描述SqlDataReader 列的元数据的DataTable。

关于ADO.NET的示例和更多信息，请参考ADO.NET Examples and Best Practices for C# Programmers (Apress, 2002)，作者是William R. Vaughn 和 Peter Blackburn。

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

USE Northwind;

GO

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

  DROP PROC dbo.usp_GetCustOrders;

GO

IF OBJECT_ID('tempdb..#CustOrders') IS NOT NULL

  DROP TABLE #CustOrders;

GO