在預存程序中指定參數

發行項
03/07/2024

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

藉由指定程序參數，呼叫端程式就能夠將值傳入程序的主體。這些值在程序執行期間可用於各種用途。如果程序參數標示為 OUTPUT 參數，程序參數也可以將值傳回給呼叫端程式。

程序最多可以有 2100 個參數，每個參數各被指派名稱、資料類型和方向。您可以選擇性指派預設值給參數。

下節提供有關傳遞值至參數，以及在程序呼叫期間如何使用每個參數屬性的詳細資訊。

注意

關於本文的練習，請參閱 AdventureWorks 系列範例資料庫。如需詳細資訊，請參閱 AdventureWorks 範例資料庫。

將值傳遞至參數

以程序呼叫提供的參數值必須是常數或變數，函數名稱不可做為參數值。變數可以是使用者自訂變數或系統變數，如 @@spid。

下列範例示範傳遞參數值至 uspGetWhereUsedProductID程序。這些範例說明如何以常數和變數方式傳遞參數，以及如何使用變數傳遞函數值。

USE AdventureWorks2022;  
GO  
-- Passing values as constants.  
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';  
GO  
-- Passing values as variables.  
DECLARE @ProductID int, @CheckDate datetime;  
SET @ProductID = 819;  
SET @CheckDate = '20050225';  
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;  
GO

下列範例會傳回錯誤，因為函數無法當做參數值傳遞。

-- Try to use a function as a parameter value.  
-- This produces an error message.  
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();

請改用變數將函數值傳遞至參數，如下列範例所示：

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO

指定參數名稱

當建立程序及宣告參數名稱時，參數名稱必須以單個 @ 字元開始，而且在程序範圍中必須是唯一的。

明確為參數命名以及在程序呼叫中指定適當值給每個參數，就能以任何順序提供參數。例如，如果 my_proc 程序預期有三個參數，名稱分別為 @first、@second 和 @third，您可以將傳遞給程序的值指派給參數名稱，例如：EXECUTE my_proc @second = 2, @first = 1, @third = 3;。

注意

如果以 @parameter = value 形式提供一個參數值，所有後續的參數就必須按照此方式來提供。如果不是以 @parameter = value 形式傳遞參數值，提供值的順序就必須與 CREATE PROCEDURE 陳述式中列出參數的順序一樣 (由左到右)。最好指定參數名稱，以便有較佳的可讀性且與預存程序的未來版本相容。

警告

任何以 @parameter = value 形式傳遞的參數若有拼字錯誤，就會讓 SQL Server 產生錯誤，並導致程序無法執行。

指定參數資料類型

在 CREATE PROCEDURE 陳述式中宣告時，參數必須定義一種資料類型。參數的資料類型將決定在呼叫程序時參數可接受的值類型和範圍。例如，若將參數定義為 tinyint 資料類型，在傳遞數值至該參數時，只能接受 0 到 255 範圍內的數值。執行程序時，如果值與資料類型不相容的話，就會傳回錯誤。

指定參數預設值

如果在宣告時，已指定參數的預設值，此參數視為選擇性參數。在程序呼叫中，不需要提供值給選擇性參數。

在下列情況下會使用參數的預設值：

在程序呼叫中未指定參數值。
程序呼叫中指定 DEFAULT 關鍵字做為值。

注意

如果預設值是包含內嵌空白或標點的字元字串，或是以數字開頭 (例如 6abc)，就必須將它括在單引號中。

注意

Azure Synapse Analytics 或 Analytics Platform System (PDW) 中均不支援預設參數。

如果無法指定適當的值做為參數的預設值，您可以指定 NULL 做為預設值。在沒有參數值的狀況下執行程序時，最好讓程序傳回自訂的訊息

下列範例使用一個輸入參數 uspGetSalesYTD 來建立 @SalesPerson程序。 NULL 做為預設值指派給參數，並用於錯誤處理陳述式，以便在未指定值給 @SalesPerson 參數而執行程序時，傳回自訂錯誤訊息。

USE AdventureWorks2022;  
GO  
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.uspGetSalesYTD;  
GO  
CREATE PROCEDURE Sales.uspGetSalesYTD  
    @SalesPerson nvarchar(50) = NULL  -- NULL default value  
AS   
    SET NOCOUNT ON;   
  
-- Validate the @SalesPerson parameter.  
IF @SalesPerson IS NULL  
BEGIN  
   PRINT 'ERROR: You must specify the last name of the sales person.'  
   RETURN  
END  
-- Get the sales for the specified sales person and   
-- assign it to the output parameter.  
SELECT SalesYTD  
FROM Sales.SalesPerson AS sp  
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
WHERE LastName = @SalesPerson;  
RETURN  
GO

下列範例執行此程序。第一個陳述式會在不指定輸入值的情況下執行程序。這將造成程序中的錯誤處理陳述式傳回自訂錯誤訊息。第二個陳述式則提供輸入值並傳回預期的結果集。

-- Run the procedure without specifying an input value.  
EXEC Sales.uspGetSalesYTD;  
GO  
-- Run the procedure with an input value.  
EXEC Sales.uspGetSalesYTD N'Blythe';  
GO

雖然可以省略已提供預設值的參數，但只能截斷不可為 Null 的參數清單。例如，如果程序有五個參數，且使用 @parameter = value 而未指定參數名稱，則第四個和第五個參數可以省略。但除非是以 @parameter = value 形式提供參數，否則只要包含第五個參數，就不能省略第四個參數。

使用預設值指定多個參數

如果指定參數名稱，就可以省略參數。請考慮下列預存程序，其中包含多個具有 NULL 預設值的選擇性參數。

USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspSearchList', 'P' ) IS NOT NULL   
    DROP PROCEDURE Production.uspSearchList;  
GO  
CREATE PROCEDURE Production.uspSearchList
      @ListPrice money 
    , @ProductCategoryID int       = NULL  -- NULL default value  
    , @ProductSubcategoryID int    = NULL  -- NULL default value  
    , @ProductBusinessEntityID int = NULL  -- NULL default value  
AS  
    SET NOCOUNT ON;  
    SELECT 
        p.Name, p.Class, p.ListPrice, p.ProductID, pc.Name, psc.Name, v.Name
    FROM 
        Production.Product AS p
    INNER JOIN Production.ProductSubCategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
    INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID
    INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID
    WHERE (p.ListPrice < @ListPrice)
    AND   (pc.ProductCategoryID = @ProductCategoryID or @ProductCategoryID IS NULL)
    AND   (psc.ProductSubcategoryID = @ProductSubcategoryID or @ProductSubcategoryID IS NULL)
    AND   (pv.BusinessEntityID = @ProductBusinessEntityID or @ProductBusinessEntityID IS NULL);
GO

您可以指定或省略具有預設值的參數，如下列範例所示，只要每個參數都以 @parameter = value 形式提供其參數名稱：

--Find all Products with a list price less than 150.00 and in the ProductCategoryID = 4
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36
EXEC Production.uspSearchList @ListPrice = 150, @ProductSubCategoryID = 36;
--Find all Products with a list price less than 150.00 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductBusinessEntityID = 1498;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4, @ProductBusinessEntityID = 1498;

下列範例不是有效的 T-SQL 語法，因為一旦提供某個參數名稱，所有後續參數都必須以相同方式提供。一律建議為所有值提供參數名稱，以防止錯誤和混淆。

EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;

指定參數方向

參數的方向可以是輸入或輸出，前者指將值傳入程序的主體，後者則指程序傳回值給呼叫端程式。預設是輸入參數。

若要指定輸出參數，必須在 CREATE PROCEDURE 陳述式的參數定義中指定 OUTPUT 關鍵字。程序結束時，會將輸出參數目前的值傳回給呼叫端程式。呼叫端程式在執行程序時，也必須使用 OUTPUT 關鍵字，才能將參數值儲存在變數中，供呼叫端程式使用。

下列範例會建立 Production.usp_GetList 程序，以傳回價格不超過指定金額的產品清單。此範例顯示使用多個 SELECT 陳述式和多個 OUTPUT 參數。 OUTPUT 參數可讓外部程序、批次，或多個 Transact-SQL 陳述式在程序執行期間存取值集。

USE AdventureWorks2022;  
GO  
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL   
    DROP PROCEDURE Production.uspGetList;  
GO  
CREATE PROCEDURE Production.uspGetList 
      @Product varchar(40)   
    , @MaxPrice money   
    , @ComparePrice money OUTPUT  
    , @ListPrice money OUT  
AS  
    SET NOCOUNT ON;  
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'  
    FROM Production.Product AS p  
    JOIN Production.ProductSubcategory AS s   
      ON p.ProductSubcategoryID = s.ProductSubcategoryID  
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;  
-- Populate the output variable @ListPprice.  
SET @ListPrice = (SELECT MAX(p.ListPrice)  
        FROM Production.Product AS p  
        JOIN  Production.ProductSubcategory AS s   
          ON p.ProductSubcategoryID = s.ProductSubcategoryID  
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);  
-- Populate the output variable @compareprice.  
SET @ComparePrice = @MaxPrice;  
GO

執行 usp_GetList 以傳回成本低於 $700 的 Adventure Works 產品 (Bikes) 清單。您可以搭配流程控制語言使用輸出參數 @cost 和 @compareprices，以在 [訊息] 視窗中傳回訊息。

注意

在建立程序過程以及在使用變數過程中，必須定義 OUTPUT 變數。參數名稱和變數名稱不一定要相符。但是，資料類型和參數定位必須相符 (除非使用 @listprice = variable)。

DECLARE @ComparePrice money, @Cost money ;  
EXECUTE Production.uspGetList '%Bikes%', 700,   
    @ComparePrice OUT,   
    @Cost OUTPUT  
IF @Cost <= @ComparePrice   
BEGIN  
    PRINT 'These products can be purchased for less than   
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'  
END  
ELSE  
    PRINT 'The prices for all products in this category exceed   
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.';

部分結果集如下：

Product                                            List Price  
-------------------------------------------------- ------------------  
Road-750 Black, 58                                 539.99  
Mountain-500 Silver, 40                            564.99  
Mountain-500 Silver, 42                            564.99  
...  
Road-750 Black, 48                                 539.99  
Road-750 Black, 52                                 539.99  
  
(14 row(s) affected)  
  
These items can be purchased for less than $700.00.