从存储过程中返回数据

项目
01/23/2025

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsMicrosoft Fabric SQL 数据库

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

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可以从 Microsoft SQL Server 示例和社区项目主页下载该数据库。

使用结果集返回数据

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

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

此示例显示的存储过程将返回所有 LastName 行（也显示在 SalesYTD 视图中）的 SalesPerson 和 vEmployee 值。

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
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO

使用输出参数返回数据

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

输出参数的示例

以下示例显示有一个输入参数和一个输出参数的过程。 @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
     INNER 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 AS 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 变量赋值。执行的语句会将 @SalesYTDBySalesPerson 变量值传递到 @SalesYTD 输出参数中。然后，在过程主体中，可以将该值用于生成新值的计算。新值可以通过输出参数重新从过程传回，在过程退出时更新 @SalesYTDBySalesPerson 变量的值。这通常称为“引用传递”功能。

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

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

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

注意

游标数据类型不能通过数据库 API（如 OLE DB、ODBC、ADO 和 DB 库）绑定到应用程序变量。由于必须在应用程序执行过程之前绑定输出参数，因此不能从数据库 API 调用具有游标输出参数的过程。只有将 cursor 输出变量分配给 Transact-SQL 局部 cursor 变量时，才可以通过 Transact-SQL 批处理、过程或触发器调用这些过程。

cursor 输出参数的规则

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

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

注意

空结果集与 null 值不同。
对于可滚动游标，在过程退出时，结果集中的所有行均会返回给调用批处理、过程或触发器。返回时，游标保留在过程中最后一次执行提取时的位置。
对于任意类型的游标，如果游标关闭，则将 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 AS CURSOR;

EXECUTE dbo.uspCurrencyCursor
    @CurrencyCursor = @MyCursor OUTPUT;

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

CLOSE @MyCursor;

DEALLOCATE @MyCursor;
GO

使用返回代码返回数据

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

DECLARE @result AS 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来检查是否存在 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
     INNER 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
EXECUTE Sales.usp_GetSalesYTD;
GO

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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 以接收过程的输出值和返回代码。
使用为 Sales.usp_GetSalesYTD 指定的输入值执行 @SalesPerson 过程，将输出值和返回代码保存在变量中。
检查 @ret_code 中的返回代码并调用 PRINT 以显示适当的消息。

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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 输出参数的示例

使用返回代码返回数据

返回代码的示例

相关内容

反馈

其他资源