从存储过程中返回数据

项目
01/23/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)

可通过三种方法将数据从过程返回到调用程序：结果集、输出参数和返回代码。本文提供了有关这三种方法的信息。

使用结果集返回数据

如果存储过程的正文中包含 SELECT 语句（而不是 SELECT ...INTO 或 INSERT...SELECT），则 SELECT 语句指定的行将直接发送到客户端。对于较大的结果集，在将结果集完全发送到客户端之前，存储过程不会继续执行下一个语句。对于较小的结果集，存储过程将对结果进行后台处理以便返回给客户端，并继续执行。如果在执行存储过程期间运行多个此类 SELECT 语句，则会将多个结果集发送到客户端。此行为也适用于嵌套 Transact-SQL 批处理、嵌套存储过程和顶级 Transact-SQL 批处理。

使用结果集返回数据的示例

下面的示例使用 AdventureWorks2022示例数据库。此示例显示的存储过程将返回所有 SalesPerson 行（也显示在 vEmployee 视图中）的 LastName 和 SalesYTD 值。

USE AdventureWorks2022;  
GO

IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL  
   DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;  
GO  

CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD  
AS    
   SET NOCOUNT ON;

   SELECT LastName, SalesYTD  
   FROM Sales.SalesPerson AS sp  
   JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID;
   
   RETURN;
GO

使用输出参数返回数据

如果在过程定义中为参数指定 output 关键字，则过程在退出时可将该参数的当前值返回给调用程序。若要将参数值保存在可在调用程序中使用的变量中，调用程序在执行过程时必须使用 output 关键字。有关可用作输出参数的数据类型的详细信息，请参阅 CREATE PROCEDURE (Transact-SQL)。

输出参数的示例

以下示例显示有一个输入参数和一个输出参数的过程。 @SalesPerson 参数将接收由调用程序指定的输入值。 SELECT 语句使用传递给输入参数的值来获取正确的 SalesYTD 值。 SELECT 语句还将该值赋给 @SalesYTD 输出参数，该参数在过程退出时将值返回给调用程序。

USE AdventureWorks2022;  
GO  
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;  
GO  
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD  
    @SalesPerson nvarchar(50),  
    @SalesYTD money OUTPUT  
AS    
  
    SET NOCOUNT ON;

    SELECT @SalesYTD = SalesYTD  
    FROM Sales.SalesPerson AS sp  
    JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
    WHERE LastName = @SalesPerson;

    RETURN;
GO

以下示例调用在第一个示例中创建的过程并将从调用的过程返回的输出参数 @SalesYTD 保存在 @SalesYTDBySalesPerson 变量中。

示例：

声明变量 @SalesYTDBySalesPerson 以接收过程的输出值。
执行为输入参数指定姓氏的 Sales.uspGetEmployeeSalesYTD 过程。将输出值保存在变量 @SalesYTDBySalesPerson 中。
调用 PRINT 以显示保存到 @SalesYTDBySalesPerson 的值。

DECLARE @SalesYTDBySalesPerson money;

EXECUTE Sales.uspGetEmployeeSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  

PRINT 'Year-to-date sales for this employee is ' +   
    CONVERT(varchar(10),@SalesYTDBySalesPerson);  
GO

也可以在执行过程时为输出参数指定输入值。这将允许过程从调用程序接收值，使用该值更改或执行操作，然后将新值返回给调用程序。在上一个示例中，可以在程序调用 @SalesYTDBySalesPerson 过程前为 Sales.uspGetEmployeeSalesYTD 变量赋值。 execute 语句将 @SalesYTDBySalesPerson 变量值传递给 @SalesYTD 输出参数。然后，在过程主体中，可以将该值用于生成新值的计算。新值可以通过输出参数重新从过程传回，在过程退出时更新 @SalesYTDBySalesPerson 变量的值。这常常被称作“传址调用功能”。

如果在调用过程时为参数指定输出，而在过程定义中该参数又不是用输出定义的，那么将收到一条错误消息。但是，在执行过程时，可以执行带有输出参数的过程而不指定输出。这样不会返回错误，但将无法在调用程序中使用输出值。

在输出参数中使用 cursor 数据类型

Transact-SQL 过程只能将 cursor 数据类型用于输出参数。如果为某个参数指定了 cursor 数据类型，则在过程定义中必须为该参数指定 varying 和 output 关键字。可以将参数指定为仅限输出，但是如果在参数声明中指定了 varying 关键字，则数据类型必须为 cursor 并且也必须指定 output 关键字。

注意

cursor 数据类型不能通过数据库 API（例如 OLE DB、ODBC、ADO 和 DB-Library）绑定到应用程序变量上。因为必须先绑定输出参数，应用程序才可以执行过程，所以带有 cursor 输出参数的过程不能通过数据库 API 调用。只有将 cursor 输出变量分配给 Transact-SQL 局部 cursor 变量时，才可以通过 Transact-SQL 批处理、过程或触发器调用这些过程。

cursor 输出参数的规则

在执行过程时，以下规则适用于 cursor 输出参数：

对于只进游标，游标的结果集中返回的行只是那些过程执行结束时处于或超出游标位置的行，例如：
- 在过程中的名为 RS 的 100 行结果集上打开一个非滚动游标。
- 过程提取结果集 RS 的前 5 行。
- 过程返回到其调用者。
- 返回到调用者的结果集 RS 由 RS 的第 6 到 100 行组成，调用者中的游标位于 RS 第一行的前面。
对于只进游标，如果过程退出时游标位于第一行的前面，则整个结果集将返回给调用批处理、过程或触发器。返回时，游标将位于第一行的前面。
对于只进游标，如果过程退出时游标的位置超出最后一行的结尾，则为调用批处理、过程或触发器返回空结果集。

注意

空结果集与空值不同。
对于可滚动游标，在过程退出时，结果集中的所有行均会返回给调用批处理、过程或触发器。返回时，游标保留在过程中最后一次执行提取时的位置。
对于任意类型的游标，如果游标关闭，则将 Null 值传递回调用批处理、过程或触发器。如果将游标指派给一个参数，但该游标从未打开过，也会出现这种情况。

注意

关闭状态只有在返回时才有影响。例如，可以在过程中关闭游标，稍后再打开游标，然后将该游标的结果集返回给调用批处理、过程或触发器。

cursor 输出参数的示例

下例创建使用 cursor 数据类型指定输出参数 @CurrencyCursor 的过程。然后在批处理中调用该过程。

首先，创建在 Currency 表上声明并打开一个游标的过程。

USE AdventureWorks2022;  
GO

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL  
    DROP PROCEDURE dbo.uspCurrencyCursor;  
GO

CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;

    SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  

    OPEN @CurrencyCursor;  
GO

接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。

USE AdventureWorks2022;  
GO

DECLARE @MyCursor CURSOR;  

EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  

WHILE (@@FETCH_STATUS = 0)  
    BEGIN;  
         FETCH NEXT FROM @MyCursor;  
    END;  

CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO

使用返回代码返回数据

过程可以返回一个整数值（称为“返回代码”），以指示过程的执行状态。使用 RETURN 语句指定过程的返回代码。与输出参数一样，执行过程时必须将返回代码保存到变量中，才能在调用程序中使用返回代码值。例如，数据类型 @result 的赋值变量 int 用于存储来自过程 my_proc 的返回代码，如：

DECLARE @result int;

EXECUTE @result = my_proc;
GO

返回代码通常用在过程内的控制流块中，以便为每种可能的错误情况设置返回代码值。可以在 Transact-SQL 语句后使用 @@ERROR 函数，来检测该语句执行过程中是否有错误发生。在 Transact-SQL 中引入 TRY/CATCH/THROW 错误处理之前，有时需要通过返回代码来确定存储过程是否成功。存储过程应始终指示因某错误而失败（如有必要，可使用 THROW/RAISERROR 生成错误），而不依赖于通过返回代码来指示失败。此外，还应避免使用返回代码来返回应用程序数据。

返回代码的示例

下面的示例显示了带有错误处理设置（为各种错误设置特殊返回代码值）的 usp_GetSalesYTD 过程。下表显示了由过程分配给每个可能错误的整数值，以及每个值的相应含义。

返回代码值	含义
0	成功执行。
1	未指定所需参数值。
2	指定参数值无效。
3	获取销售额数值时出错。
4	该销售人员的销售额数值为 NULL。

该示例创建名为 Sales.usp_GetSalesYTD 的过程，该过程：

声明 @SalesPerson 参数，并将其默认值设置为 NULL。此参数旨在获取销售人员的姓氏。
验证 @SalesPerson 参数。
- 如果 @SalesPerson 为 NULL，则该过程将输出一条消息并返回返回代码 1。
- 否则，如果 @SalesPerson 参数不为 NULL，则过程将检查 HumanResources.vEmployee 表中姓氏等于 @SalesPerson 的值的行计数。如果计数为零，则过程返回返回代码 2。
查询具有指定姓氏的销售人员的年初至今销售额，并将其分配给 @SalesYTD 输出参数。
通过测试 @@ERROR (Transact-SQL) 检查 SQL Server 错误。
- 如果 @@ERROR 不等于零，则过程返回返回代码 3。
- 如果 @@ERROR 等于零，则过程将检查 @SalesYTD 参数值是否为 NULL。如果找不到年初至今的销售额，该过程将返回返回代码 4。
- 如果上述两个条件都不成立，则过程返回返回代码 0。
如果达到了，则存储过程中的最后一个语句以递归方式调用存储过程，而无需指定输入值。

在示例末尾，提供了代码来执行 Sales.usp_GetSalesYTD 过程，同时为输入参数指定姓氏，将输出值保存在变量 @SalesYTD 中。

USE AdventureWorks2022;  
GO
  
CREATE PROCEDURE Sales.usp_GetSalesYTD 
    @SalesPerson NVARCHAR(50) = NULL, 
    @SalesYTD MONEY=NULL OUTPUT
AS
    IF @SalesPerson IS NULL 
    BEGIN
        PRINT 'ERROR: You must specify a last name for the sales person.'
        RETURN (1)
    END
    ELSE 
    BEGIN
        IF(SELECT COUNT(*)FROM HumanResources.vEmployee WHERE LastName=@SalesPerson)=0
            RETURN (2)
    END

    SELECT @SalesYTD=SalesYTD
    FROM Sales.SalesPerson AS sp
         JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID=sp.BusinessEntityID
    WHERE LastName=@SalesPerson;

    IF @@ERROR<>0 
    BEGIN
        RETURN (3)
    END 
    ELSE 
    BEGIN
        IF @SalesYTD IS NULL 
            RETURN (4)
        ELSE 
            RETURN (0)
    END

    EXEC Sales.usp_GetSalesYTD;
GO


DECLARE @SalesYTDForSalesPerson money, @ret_code int;  

EXECUTE Sales.usp_GetSalesYTD  N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  

PRINT N'Year-to-date sales for this employee is ' +  
    CONVERT(varchar(10), @SalesYTDForSalesPerson);  
GO

下面的示例创建了处理从 usp_GetSalesYTD 过程返回的返回代码的程序。

示例：

声明变量 @SalesYTDForSalesPerson 和 @ret_code 以接收过程的输出值和返回代码。
使用为 @SalesPerson 指定的输入值执行 Sales.usp_GetSalesYTD 过程，将输出值和返回代码保存在变量中。
检查 @ret_code 中的返回代码，并调用 PRINT (Transact-SQL) 以显示相应的消息。


DECLARE @SalesYTDForSalesPerson money, @ret_code int;  
  
EXECUTE @ret_code = Sales.usp_GetSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  

IF @ret_code = 0  
    BEGIN  
        PRINT 'Procedure executed successfully';
        PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson);
    END  
ELSE IF @ret_code = 1  
   PRINT 'ERROR: You must specify a last name for the sales person.';
ELSE IF @ret_code = 2   
   PRINT 'ERROR: You must enter a valid last name for the sales person.';
ELSE IF @ret_code = 3  
   PRINT 'ERROR: An error occurred getting sales value.';
ELSE IF @ret_code = 4  
   PRINT 'ERROR: No sales recorded for this employee.';
GO

有关存储过程和相关概念的详细信息，请参阅以下文章：

从存储过程中返回数据

使用结果集返回数据

使用结果集返回数据的示例

使用输出参数返回数据

输出参数的示例

在输出参数中使用 cursor 数据类型

cursor 输出参数的规则

cursor 输出参数的示例

使用返回代码返回数据

返回代码的示例

反馈

反馈

其他资源

从存储过程中返回数据

使用结果集返回数据

使用结果集返回数据的示例

使用输出参数返回数据

输出参数的示例

在输出参数中使用 cursor 数据类型

cursor 输出参数的规则

cursor 输出参数的示例

使用返回代码返回数据

返回代码的示例

相关内容

反馈

反馈

其他资源