從預存程序傳回資料

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體Azure Synapse AnalyticsMicrosoft Fabric 中的 SQL 資料庫

將資料從程序傳回至呼叫端程式的方式有三種：結果集、輸出參數和傳回碼。 本文提供三種方法的相關資訊。

本文中的程式代碼範例會使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，您可以從 Microsoft SQL Server 範例和社群專案 首頁下載。

使用結果集傳回資料

如果您在預存程式的主體中包含 SELECT 語句（但不包含 SELECT ... INTO 或 INSERT ... SELECT），則 SELECT 語句所指定的數據列會直接傳送至用戶端。 針對大型結果集，在結果集完全傳送至用戶端之前，預存程序執行不會繼續至下一個陳述式。 針對小型結果集，結果會被緩衝以返回給用戶端，然後繼續執行。 如果在預存程式執行期間執行多個這類 SELECT 語句，則會將多個結果集傳送至用戶端。 此行為也適用於巢狀 Transact-SQL 批次、巢狀預存程序和最上層 Transact-SQL 批次。

使用結果集傳回資料的範例

此範例顯示一個預存程序，可傳回 LastName 和 SalesYTD 值，針對所有也出現在 vEmployee 檢視中的 SalesPerson 資料列。

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

使用輸出參數傳回資料

如果在程序定義中為參數指定輸出關鍵字，程序就可以在結束時將參數目前的值傳回至呼叫端程式。 若要將參數值儲存在可供呼叫端程式使用的變數中，呼叫端程式在執行程序時必須使用輸出關鍵字。 如需哪些資料類型可作為輸出參數的詳細資訊，請參閱 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 變數的值。 executed 語句會將 @SalesYTDBySalesPerson 變數值傳遞至 @SalesYTD 輸出參數。 然後在程序主體中，此值可用於產生新值的計算。 程序結束時，新值可透過輸出參數從程序傳回，並更新 @SalesYTDBySalesPerson 變數中的值。 這通常稱為 參考傳遞 能力。

呼叫程序時，如果您對參數指定輸出，但該參數在程序定義中並未使用輸出來定義，則會出現錯誤訊息。 不過，您可以用輸出參數來執行程序，但在執行程序時不指定輸出。 此操作不會傳回錯誤，但您不能在呼叫程式中使用輸出值。

在輸出參數中使用 cursor 資料類型

Transact-SQL 程序只能針對輸出參數使用 cursor 資料類型。 如果為參數指定 cursor 資料類型，程序定義中也必須為該參數指定變異和輸出關鍵字。 參數可以指定為僅限輸出，但如果在參數宣告中指定變異關鍵字，資料類型必須是 cursor，同時也必須指定輸出關鍵字。

注意

數據指標數據類型無法透過 OLE DB、ODBC、ADO 和 DB-Library 等資料庫 API 系結至應用程式變數。 由於輸出參數必須先系結，應用程式才能執行程式，因此無法從資料庫 API 呼叫具有數據指標輸出參數的程式。 只有當游標輸出變數被指派給「Transact-SQL」本機游標變數時，才可以從「Transact-SQL」批次、程序或觸發器呼叫這些程序。

Cursor 輸出參數的規則

以下規則是有關程序執行時的 cursor 輸出參數：

• 如果是順向唯讀游標，在程序執行結束時，游標結果集中返回的資料列只包括游標當前位置及其後的資料列。 例如：

  • 一個無法捲動的資料指標在 RS 結果集 (有 100 個資料列) 的程序中開啟。

  • 此程序擷取 RS 結果集的前五個資料列。

  • 此函式返回呼叫者。

  • 傳回呼叫者的 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來指定程式的傳回碼。 就像使用輸出參數一樣，若要在呼叫端程式中使用傳回碼值，您必須在執行程序時將傳回碼儲存在變數中。 例如，@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。
  • 如果上述所有條件都不是 True，程序會傳回傳回碼 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

相關內容

• CREATE PROCEDURE (Transact-SQL)
• PRINT (Transact-SQL)
• SET @local_variable (Transact-SQL)
• RETURN (Transact-SQL)
• @@ERROR (Transact-SQL)