ストアドプロシージャ内でパラメーター値を指定します。

[アーティクル]
07/19/2024

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

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

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

Note

この記事の演習については、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 つの @ 文字にし、そのプロシージャのスコープ内でパラメーター名が一意になるようにする必要があります。

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

Note

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

警告

@parameter = value の形式で渡すパラメーターのスペルが間違っていると、SQL Server によってエラーが生成され、プロシージャは実行されません。

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

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

パラメーターの既定値の指定

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

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

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

Note

既定値が空白または句読点を含む文字列の場合、または数字で始まる場合 (たとえば 6abc)、単一引用符で囲む必要があります。

Note

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

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

次の例で、 uspGetSalesYTD という 1 つの入力パラメーターを伴う @SalesPersonプロシージャを作成します。このパラメーターには既定値として NULL が割り当てられています。 @SalesPerson パラメーターに値を指定せずにプロシージャを実行した場合にカスタムエラーメッセージを返すエラー処理ステートメントで NULL を使用します。

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 番目のパラメーターを両方とも省略できます。ただし、5 番目のパラメータが指定されている限り、4 番目のパラメータを省略することはできません。@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 を使用して、 [メッセージ] ウィンドウにメッセージを返します。

Note

プロシージャの作成中にも変数の使用中にも、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.

次の方法で共有

ストアドプロシージャ内でパラメーター値を指定します。

パラメーターに値を渡す処理

パラメーター名の指定

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

パラメーターの既定値の指定

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

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

フィードバック

その他のリソース

次の方法で共有

ストアド プロシージャ内でパラメーター値を指定します。

パラメーターに値を渡す処理

パラメーター名の指定

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

パラメーターの既定値の指定

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

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

関連するコンテンツ

フィードバック

その他のリソース

ストアドプロシージャ内でパラメーター値を指定します。