从存储过程中返回数据Return Data from a Stored Procedure

本主题适用于:是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

有关与以前版本的 SQL Server 相关的内容,请参阅从存储过程中返回数据For content related to previous versions of SQL Server, see Return Data from a Stored Procedure.

可通过三种方法将数据从过程返回到调用程序:结果集、输出参数和返回代码。There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes. 本主题提供了有关这三种方法的信息。This topic provides information on the three approaches.

使用结果集返回数据Returning Data Using Result Sets

如果存储过程的正文中包含 SELECT 语句(而不是 SELECT ...INTO 或 INSERT...SELECT),则 SELECT 语句指定的行将直接发送到客户端。If you include a SELECT statement in the body of a stored procedure (but not a SELECT ... INTO or INSERT ... SELECT), the rows specified by the SELECT statement will be sent directly to the client. 对于较大的结果集,在将结果集完全发送到客户端之前,存储过程不会继续执行下一个语句。For large result sets the stored procedure execution will not continue to the next statement until the result set has been completely sent to the client. 对于较小的结果集,存储过程将对结果进行后台处理以便返回给客户端,并继续执行。For small result sets the results will be spooled for return to the client and execution will continue. 如果在执行存储过程期间运行多个此类 SELECT 语句,则会将多个结果集发送到客户端。If multiple such SELECT statements are run during the exeuction of the stored proceudre, multiple result sets will be sent to the client. 此行为也适用于嵌套 TSQL 批处理、嵌套存储过程和顶级 TSQL 批处理。This behavior also applies to nested TSQL batches, nested stored procedures and top-level TSQL batches.

使用结果集返回数据的示例Examples of Returning Data Using a Result Set

以下示例显示的存储过程将返回所有 SalesPerson 行(也显示在 vEmployee 视图中)的 LastName 和 SalesYTD 值。The following example shows a stored procedure that returns the LastName and SalesYTD values for all SalesPerson rows that also appear in the vEmployee view.

USE AdventureWorks2012;  
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  

使用输出参数返回数据Returning Data Using an Output Parameter

如果在过程定义中为参数指定 OUTPUT 关键字,则过程在退出时可将该参数的当前值返回给调用程序。If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. 若要将参数值保存在可在调用程序中使用的变量中,调用程序在执行过程时必须使用 OUTPUT 关键字。To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure. 有关可用作输出参数的数据类型的详细信息,请参阅 CREATE PROCEDURE (Transact-SQL)For more information about what data types can be used as output parameters, see CREATE PROCEDURE (Transact-SQL).

输出参数的示例Examples of Output Parameter

以下示例显示有一个输入参数和一个输出参数的过程。The following example shows a procedure with an input and an output parameter. @SalesPerson 参数将接收由调用程序指定的输入值。The @SalesPerson parameter would receive an input value specified by the calling program. SELECT 语句使用传递给输入参数的值来获取正确的 SalesYTD 值。The SELECT statement uses the value passed into the input parameter to obtain the correct SalesYTD value. SELECT 语句还将该值赋给 @SalesYTD 输出参数,该参数在过程退出时将值返回给调用程序。The SELECT statement also assigns the value to the @SalesYTD output parameter, which returns the value to the calling program when the procedure exits.

USE AdventureWorks2012;  
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 变量中,该变量是调用程序的局部变量。The following example calls the procedure created in the first example and saves the output value returned from the called procedure in the @SalesYTD variable, which is local to the calling program.

-- Declare the variable to receive the output value of the procedure.  
DECLARE @SalesYTDBySalesPerson money;  
-- Execute the procedure specifying a last name for the input parameter  
-- and saving the output value in the variable @SalesYTDBySalesPerson  
EXECUTE Sales.uspGetEmployeeSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  
-- Display the value returned by the procedure.  
PRINT 'Year-to-date sales for this employee is ' +   
    convert(varchar(10),@SalesYTDBySalesPerson);  
GO  

也可以在执行过程时为 OUTPUT 参数指定输入值。Input values can also be specified for OUTPUT parameters when the procedure is executed. 这将允许过程从调用程序接收值,使用该值更改或执行操作,然后将新值返回给调用程序。This allows the procedure to receive a value from the calling program, change or perform operations with the value, and then return the new value to the calling program. 在上一个示例中,可以在程序调用 @SalesYTDBySalesPerson 过程前为 Sales.uspGetEmployeeSalesYTD 变量赋值。In the previous example, the @SalesYTDBySalesPerson variable can be assigned a value before the program calls the Sales.uspGetEmployeeSalesYTD procedure. execute 语句将 @SalesYTDBySalesPerson 变量值传递给 @SalesYTD OUTPUT 参数。The execute statement would pass the @SalesYTDBySalesPerson variable value into the @SalesYTD OUTPUT parameter. 然后,在过程主体中,可以将该值用于生成新值的计算。Then in the procedure body, the value could be used for calculations that generate a new value. 新值可以通过 OUTPUT 参数重新从过程传回,在过程退出时更新 @SalesYTDBySalesPerson 变量的值。The new value would be passed back out of the procedure through the OUTPUT parameter, updating the value in the @SalesYTDBySalesPerson variable when the procedure exits. 这常常被称作“传址调用功能”。This is often referred to as "pass-by-reference capability."

如果在调用过程时为参数指定 OUTPUT,而在过程定义中该参数又不是用 OUTPUT 定义的,那么将收到一条错误消息。If you specify OUTPUT for a parameter when you call a procedure and that parameter is not defined by using OUTPUT in the procedure definition, you get an error message. 但是,在执行过程时,可以执行带有输出参数的过程而不指定 OUTPUT。However, you can execute a procedure with output parameters and not specify OUTPUT when executing the procedure. 这样不会返回错误,但将无法在调用程序中使用输出值。No error is returned, but you cannot use the output value in the calling program.

在 OUTPUT 参数中使用 cursor 数据类型Using the Cursor Data Type in OUTPUT Parameters

Transact-SQLTransact-SQL 过程只能将 cursor 数据类型用于 OUTPUT 参数。 procedures can use the cursor data type only for OUTPUT parameters. 如果为某个参数指定了 cursor 数据类型,在过程定义中必须为该参数指定 VARYING 和 OUTPUT 关键字。If the cursor data type is specified for a parameter, both the VARYING and OUTPUT keywords must be specified for that parameter in the procedure definition. 可以将参数指定为仅限 OUTPUT,但是如果在参数声明中指定了 VARYING 关键字,则数据类型必须为 cursor 并且也必须指定 OUTPUT 关键字。A parameter can be specified as only OUTPUT but if the VARYING keyword is specified in the parameter declaration, the data type must be cursor and the OUTPUT keyword must also be specified.

备注

cursor 数据类型不能通过数据库 API(例如 OLE DB、ODBC、ADO 和 DB-Library)绑定到应用程序变量上。The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. 因为必须先绑定 OUTPUT 参数,应用程序才可以执行过程,所以带有 cursor OUTPUT 参数的过程不能通过数据库 API 调用。Because OUTPUT parameters must be bound before an application can execute a procedure, procedures with cursor OUTPUT parameters cannot be called from the database APIs. 只有将 Transact-SQLTransact-SQL cursor OUTPUT 变量分配给 局部 Transact-SQLTransact-SQL cursor 变量时,才可以通过 批处理、过程或触发器调用这些过程。These procedures can be called from Transact-SQLTransact-SQL batches, procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQLTransact-SQL local cursor variable.

cursor 输出参数的规则Rules for Cursor Output Parameters

在执行过程时,以下规则适用于 cursor 输出参数:The following rules pertain to cursor output parameters when the procedure is executed:

  • 对于只进游标,游标的结果集中返回的行只是那些过程执行结束时处于或超出游标位置的行,例如:For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the procedure execution, for example:

    • 在过程中的名为 RS 的 100 行结果集上打开一个非滚动游标。A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.

    • 过程提取结果集 RS 的头 5 行。The procedure fetches the first 5 rows of result set RS.

    • 过程返回到其调用者。The procedure returns to its caller.

    • 返回到调用者的结果集 RS 由 RS 的第 6 到 100 行组成,调用者中的游标处于 RS 的第一行之前。The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.

  • 对于只进游标,如果过程退出时游标位于第一行的前面,则整个结果集将返回给调用批处理、过程或触发器。For a forward-only cursor, if the cursor is positioned before the first row when the procedure exits, the entire result set is returned to the calling batch, procedure, or trigger. 返回时,游标将位于第一行的前面。When returned, the cursor position is set before the first row.

  • 对于只进游标,如果过程退出时游标的位置超出最后一行的结尾,则为调用批处理、过程或触发器返回空结果集。For a forward-only cursor, if the cursor is positioned beyond the end of the last row when the procedure exits, an empty result set is returned to the calling batch, procedure, or trigger.

    备注

    空结果集与空值不同。An empty result set is not the same as a null value.

  • 对于可滚动游标,在过程退出时,结果集中的所有行均会返回给调用批处理、过程或触发器。For a scrollable cursor, all the rows in the result set are returned to the calling batch, procedure, or trigger when the procedure exits. 返回时,游标保留在过程中最后一次执行提取时的位置。When returned, the cursor position is left at the position of the last fetch executed in the procedure.

  • 对于任意类型的游标,如果游标关闭,则将 Null 值传递回调用批处理、过程或触发器。For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, procedure, or trigger. 如果将游标指派给一个参数,但该游标从未打开过,也会出现这种情况。This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

    备注

    关闭状态只有在返回时才有影响。The closed state matters only at return time. 例如,可以在过程中关闭游标,稍后再打开游标,然后将该游标的结果集返回给调用批处理、过程或触发器。For example, it is valid to close a cursor part of the way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, procedure, or trigger.

cursor 输出参数的示例Examples of Cursor Output Parameters

下例创建使用 cursor 数据类型指定输出参数 @currency_cursor 的过程。In the following example, a procedure is created that specified an output parameter, @currency_cursor using the cursor data type. 然后在批处理中调用该过程。The procedure is then called in a batch.

首先,创建以下过程,在 Currency 表上声明并打开一个游标。First, create the procedure that declares and then opens a cursor on the Currency table.

USE AdventureWorks2012;  
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  

接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

USE AdventureWorks2012;  
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  

使用返回代码返回数据Returning Data Using a Return Code

过程可以返回一个整数值(称为“返回代码”),以指示过程的执行状态。A procedure can return an integer value called a return code to indicate the execution status of a procedure. 使用 RETURN 语句指定过程的返回代码。You specify the return code for a procedure using the RETURN statement. 与 OUTPUT 参数一样,执行过程时必须将返回代码保存到变量中,才能在调用程序中使用返回代码值。As with OUTPUT parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program. 例如,数据类型 @result int 的赋值变量 用于存储来自过程 my_proc的返回代码,如:For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc, such as:

DECLARE @result int;  
EXECUTE @result = my_proc;  

返回代码通常用在过程内的控制流块中,以便为每种可能的错误情况设置返回代码值。Return codes are commonly used in control-of-flow blocks within procedures to set the return code value for each possible error situation. 可以在 Transact-SQLTransact-SQL 语句后使用 @@ERROR 函数,来检测该语句执行过程中是否有错误发生。You can use the @@ERROR function after a Transact-SQLTransact-SQL statement to detect whether an error occurred during the execution of the statement. 在 TSQL 中引入 TRY/CATCH/THROW 错误处理之前,有时需要通过返回代码来确定存储过程是否成功。Before the introduction of TRY/CATCH/THROW error handling in TSQL return codes were sometimes required to determine the success or failure of stored procedures. 存储过程应始终指示因某错误而失败(如有必要,可使用 THROW/RAISERROR 生成错误),而不依赖于通过返回代码来指示失败。Stored Procedures should always indicate failure with an error (generated with THROW/RAISERROR if neccessary), and not rely on a return code to indicate the failure. 此外,还应避免使用返回代码来返回应用程序数据。Also you should avoid using the return code to return application data.

返回代码的示例Examples of Return Codes

下面的示例显示了带有错误处理设置(为各种错误设置特殊返回代码值)的 usp_GetSalesYTD 过程。The following example shows the usp_GetSalesYTD procedure with error handling that sets special return code values for various errors. 下表显示了由过程分配给每个可能错误的整数值,以及每个值的相应含义。The following table shows the integer value that is assigned by the procedure to each possible error, and the corresponding meaning for each value.

返回代码值Return code value 含义Meaning
00 成功执行。Successful execution.
@shouldalert1 未指定所需参数值。Required parameter value is not specified.
22 指定参数值无效。Specified parameter value is not valid.
33 获取销售额数值时出错。Error has occurred getting sales value.
44 该销售人员的销售额数值为 NULL。NULL sales value found for the salesperson.
USE AdventureWorks2012;  
GO  
IF OBJECT_ID('Sales.usp_GetSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.usp_GetSalesYTD;  
GO  
CREATE PROCEDURE Sales.usp_GetSalesYTD  
@SalesPerson nvarchar(50) = NULL,  -- NULL default value  
@SalesYTD money = NULL OUTPUT  
AS    

-- Validate the @SalesPerson parameter.  
IF @SalesPerson IS NULL  
   BEGIN  
       PRINT 'ERROR: You must specify a last name for the sales person.'  
       RETURN(1)  
   END  
ELSE  
   BEGIN  
   -- Make sure the value is valid.  
   IF (SELECT COUNT(*) FROM HumanResources.vEmployee  
          WHERE LastName = @SalesPerson) = 0  
      RETURN(2)  
   END  
-- Get the sales for the specified name and   
-- assign it to the output parameter.  
SELECT @SalesYTD = SalesYTD   
FROM Sales.SalesPerson AS sp  
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
WHERE LastName = @SalesPerson;  
-- Check for SQL Server errors.  
IF @@ERROR <> 0   
   BEGIN  
      RETURN(3)  
   END  
ELSE  
   BEGIN  
   -- Check to see if the ytd_sales value is NULL.  
     IF @SalesYTD IS NULL  
       RETURN(4)   
     ELSE  
      -- SUCCESS!!  
        RETURN(0)  
   END  
-- Run the stored procedure without specifying an input value.  
EXEC Sales.usp_GetSalesYTD;  
GO  
-- Run the stored procedure with an input value.  
DECLARE @SalesYTDForSalesPerson money, @ret_code int;  
-- Execute the procedure specifying a last name for the input parameter  
-- and saving the output value in the variable @SalesYTD  
EXECUTE Sales.usp_GetSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  
PRINT N'Year-to-date sales for this employee is ' +  
    CONVERT(varchar(10), @SalesYTDForSalesPerson);  

下面的示例创建了处理从 usp_GetSalesYTD 过程返回的返回代码的程序。The following example creates a program to handle the return codes that are returned from the usp_GetSalesYTD procedure.

-- Declare the variables to receive the output value and return code   
-- of the procedure.  
DECLARE @SalesYTDForSalesPerson money, @ret_code int;  

-- Execute the procedure with a title_id value  
-- and save the output value and return code in variables.  
EXECUTE @ret_code = Sales.usp_GetSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  
--  Check the return codes.  
IF @ret_code = 0  
BEGIN  
   PRINT 'Procedure executed successfully'  
   -- Display the value returned by the procedure.  
   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 'EERROR: 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  

另请参阅See Also

DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
PRINT (Transact-SQL) PRINT (Transact-SQL)
SET @local_variable (Transact-SQL) SET @local_variable (Transact-SQL)
游标 Cursors
RETURN (Transact-SQL) RETURN (Transact-SQL)
@@ERROR (Transact-SQL)@@ERROR (Transact-SQL)