ストアドプロシージャでパラメーターを指定する

[アーティクル]
05/25/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

プロシージャのパラメーターを指定することで、呼び出し元のプログラムからプロシージャの本体に値を渡すことができます。これらの値は、プロシージャの実行中にさまざまな目的で使用できます。プロシージャパラメーターも、パラメーターが OUTPUT パラメーターとしてマークされている場合は、呼び出し元のプログラムに値を返すことができます。

プロシージャには最大 2,100 個のパラメーターを指定できます。各パラメーターには、名前、データ型、および方向が割り当てられます。パラメーターには、必要に応じて既定値を割り当てることもできます。

次のセクションでは、パラメーターに値を渡す処理と、プロシージャ呼び出しで各パラメーター属性を使用する方法について説明します。

注意

この記事の演習については、 AdventureWorks 一連のサンプルデータベースを参照してください。詳細については、「AdventureWorks sample databases」 (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

パラメーター名を指定する

プロシージャを作成してパラメーター名を宣言する際には、パラメーター名の先頭を 1 つの @ 文字にし、そのプロシージャのスコープ内でパラメーター名が一意になるようにする必要があります。

パラメーターに明示的に名前を付け、プロシージャ呼び出しで各パラメーターに適切な値を代入することで、パラメーターを任意の順序で指定できます。たとえば、プロシージャで、@second、および @thirdという名前@firstの 3 つのパラメーターが必要な場合、プロシージャに渡される値をパラメーター名に割り当てることができます (例: EXECUTE my_proc @second = 2, @first = 1, @third = 3;)。my_proc

注意

形式 @parameter = valueで 1 つのパラメーター値を指定する場合は、後続のすべてのパラメーターをこの方法で指定する必要があります。パラメーター値がという形式 @parameter = valueで渡されない場合は、CREATE PROCEDURE ステートメントにパラメーターが一覧表示されているのと同じ順序 (左から右) で値を指定する必要があります。ストアドプロシージャの将来のバージョンとの読みやすさと互換性を高めるために、パラメーター名を指定することをお勧めします。

警告

パラメーターのスペルが間違ってフォーム@parameter = valueに渡されたパラメーターは、エラーを生成し、プロシージャの実行を妨げるSQL Serverが発生します。

パラメーターのデータ型を指定する

パラメーターを CREATE PROCEDURE ステートメントで宣言する場合は、パラメーターのデータ型を定義する必要があります。パラメーターのデータ型により、プロシージャの呼び出し時にパラメーターとして指定できる値の型と範囲が決まります。たとえば、 tinyint データ型のパラメーターを定義した場合は、そのパラメーターに渡す値として 0 ～ 255 の範囲の数値だけを指定できます。指定したデータ型と互換性がない値を使用してプロシージャを実行すると、エラーが返されます。

パラメーターの既定値を指定する

宣言時にパラメーターに既定値が指定されていると、そのパラメーターは省略可能と見なされます。プロシージャ呼び出しで省略可能なパラメーターに値を指定する必要はありません。

パラメーターの既定値は次の場合に使用されます。

プロシージャ呼び出しでパラメーターの値が指定されていない場合
プロシージャ呼び出しで値として DEFAULT キーワードが指定されている場合

注意

既定値が埋め込みブランクまたは句読点を含む文字列である場合、または数値で始まる場合 (例: )、 6abc単一引用符で囲む必要があります。

注意

既定のパラメーターは、Azure Synapse Analytics または Analytics Platform System (PDW) ではサポートされていません。

パラメーターの既定値として適切に値を指定できない場合は、既定値としてを指定 NULL します。パラメーターの値なしでプロシージャを実行する場合は、プロシージャからカスタマイズされたメッセージが返されるようにすることをお勧めします。

次の例で、 uspGetSalesYTD という 1 つの入力パラメーターを伴う @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

次の例では、プロシージャを実行します。最初のステートメントは、入力値を指定せずにプロシージャを実行します。その結果、プロシージャのエラー処理ステートメントによってカスタムエラーメッセージが返されます。 2 番目のステートメントでは入力値を渡し、予期した結果セットが返されます。

-- 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 非許容パラメーターの一覧は切り捨てることができるだけです。たとえば、プロシージャに 5 つのパラメーターがある場合は、でパラメーター名 @parameter = valueを指定せずに、4 番目と 5 番目のパラメーターを省略できます。ただし、4 番目のパラメーターは、5 番目のパラメーターが含まれている限りスキップできません。ただし、形式 @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 パラメーターを使用する例を示しています。外部プロシージャ、バッチ、または複数の Transact-SQL ステートメントからは、OUTPUT パラメーターを使用して、プロシージャの実行中に設定された値にアクセスできます。

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) の一覧を返します。ここではフロー制御言語と共に OUTPUT パラメーターの @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.

ストアドプロシージャでパラメーターを指定する

パラメーターに値を渡す

パラメーター名を指定する

パラメーターのデータ型を指定する

パラメーターの既定値を指定する

既定値を使用して複数のパラメーターを指定する

パラメーターの方向を指定する

次の手順

フィードバック

フィードバック

その他のリソース

ストアド プロシージャでパラメーターを指定する

パラメーターに値を渡す

パラメーター名を指定する

パラメーター のデータ型を指定する

パラメーターの既定値を指定する

既定値を使用して複数のパラメーターを指定する

パラメーターの方向を指定する

次の手順

フィードバック

フィードバック

その他のリソース

ストアドプロシージャでパラメーターを指定する

パラメーターのデータ型を指定する