ストアド プロシージャ内でパラメーター値を指定します。
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric SQL Database
プロシージャのパラメーターを指定することで、呼び出し元のプログラムからプロシージャの本体に値を渡すことができます。 これらの値は、プロシージャの実行中にさまざまな目的で使用できます。 プロシージャ パラメーターも、パラメーターが 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.