SQL 実行タスクのパラメーターとリターンコード

[アーティクル]
05/07/2013

SQL ステートメントとストアドプロシージャでは多くの場合、input パラメーター、output パラメーター、およびリターンコードを使用します。 Integration Services の SQL 実行タスクでは、Input、Output、および ReturnValue という、パラメーターの型がサポートされています。入力パラメーターには Input 型、出力パラメーターには Output 型、およびリターンコードには ReturnValue 型を使用します。

注
SQL 実行タスクでは、データプロバイダーがサポートしている場合のみ、パラメーターを使用できます。

クエリやストアドプロシージャなど、SQL コマンドのパラメーターは、SQL 実行タスクのスコープ内、親コンテナー、またはパッケージのスコープ内に作成されたユーザー定義変数にマップされます。変数の値はデザイン時に設定することも、実行時に動的に設定することもできます。パラメーターは、システム変数にマップすることもできます。詳細については、「Integration Services (SSIS) の変数」および「システム変数」を参照してください。

SQL 実行タスクでパラメーターやリターンコードを使用すると、タスクでサポートされるパラメーターの型やこれらのパラメーターのマップ方法を把握するだけでなく、その他の情報も取得できます。 SQL 実行タスクでパラメーターおよびリターンコードを正しく使用する際に必要となる、追加の使用要件やガイドラインがあります。以降では、こうした使用要件およびガイドラインについて説明します。

パラメーター名とパラメーターマーカーの使用
日付と時刻のデータ型のパラメーターの使用
WHERE 句でのパラメーターの使用
ストアドプロシージャでのパラメーターの使用
リターンコードの値の取得
SQL 実行タスクのパラメーターとリターンコードの構成

パラメーター名とパラメーターマーカーの使用

SQL コマンドの構文では、SQL 実行タスクが使用する接続の種類によって、異なるパラメーターマーカーが使用されます。たとえば、ADO.NET 接続マネージャーの場合は、SQL コマンドが使用するパラメーターマーカーの形式を @varParameter にする必要がありますが、OLE DB 接続の場合は疑問符 (?) パラメーターマーカーが必要です。

変数とパラメーターの間でのマッピングでパラメーター名として使用できる名前も、接続マネージャーの種類によって異なります。たとえば、ADO.NET 接続マネージャーでは @ プレフィックス付きのユーザー定義名を使用し、OLE DB 接続マネージャーではパラメーター名として 0 から始まる序数の数値を使用する必要があります。

次の表に、SQL 実行タスクで使用できる接続マネージャーの種類の SQL コマンドの要件をまとめます。

接続の種類	パラメーターマーカー	パラメーター名	SQL コマンドの例
ADO	?	Param1、Param2、...	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET	@<parameter name>	@<parameter name>	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL および OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

ADO.NET 接続マネージャーおよび ADO 接続マネージャーでのパラメーターの使用

ADO.NET 接続マネージャーおよび ADO 接続マネージャーでは、パラメーターを使用する SQL コマンドに関する特定の要件があります。

ADO.NET 接続マネージャーでは、SQL コマンド内のパラメーターマーカーとしてパラメーター名を使用することが必要になります。これは、変数をパラメーターに直接マップできることを意味します。たとえば、変数 @varName は、@parName という名前のパラメーターにマップされ、パラメーター @parName に変数を提供します。
ADO 接続マネージャーでは、SQL コマンド内のパラメーターマーカーとして疑問符 (?) を使用する必要があります。ただし、パラメーター名として任意のユーザー定義名 (整数値を除く) を使用できます。

パラメーターに値を提供するプロセスで、変数がパラメーター名にマップされます。その後、SQL 実行タスクがパラメーターリスト内にあるパラメーター名の序数値を使用して、変数からパラメーターに値を読み込みます。

EXCEL、ODBC、および OLE DB 接続マネージャーでのパラメーターの使用

EXCEL、ODBC、および OLE DB の各接続マネージャーでは、SQL コマンド内のパラメーターマーカーとして疑問符 (?) を使用し、パラメーター名として 0 または 1 から始まる序数を使用する必要があります。 SQL 実行タスクで ODBC 接続マネージャーが使用される場合、クエリの最初のパラメーターにマップされるパラメーター名は 1 になります。それ以外の場合、パラメーター名は 0 になります。後続のパラメーター名の数値は、パラメーター名のマップ先である SQL コマンドのパラメーターを示します。たとえば、3 というパラメーター名は、SQL コマンド内の 3 番目の疑問符 (?) で表される、3 番目のパラメーターにマップされます。

パラメーターに値を提供するプロセスで、変数がパラメーター名にマップされ、SQL 実行タスクがパラメーター名の序数値を使用して、変数からパラメーターに値を読み込みます。

接続マネージャーが使用するプロバイダーによっては、一部の OLE DB データ型がサポートされないことがあります。たとえば、Excel ドライバーは限定されたデータ型のセットしか認識しません。 Excel ドライバーでの Jet プロバイダーの動作の詳細については、「Excel ソース」を参照してください。

OLE DB 接続マネージャーでのパラメーターの使用

SQL 実行タスクが OLE DB 接続マネージャーを使用する場合は、タスクの BypassPrepare プロパティを使用できます。 SQL 実行タスクが、パラメーターと共に SQL ステートメントを使用する場合は、このプロパティを true に設定する必要があります。

OLE DB 接続マネージャーを使用する場合、パラメーター化サブクエリは使用できません。これは、SQL 実行タスクが OLE DB プロバイダーを介してパラメーター情報を取得できないからです。ただし、式を使用することで、パラメーター値をクエリ文字列に連結したり、タスクの SqlStatementSource プロパティを設定したりできます。

日付と時刻のデータ型のパラメーターの使用

ADO.NET 接続マネージャーおよび ADO 接続マネージャーでの日付と時刻のパラメーターの使用

SQL Server 型 (time および datetimeoffset) のデータを読み取る場合、ADO.NET 接続マネージャーまたは ADO 接続マネージャーのいずれかを使用する SQL 実行タスクには、次の追加要件があります。

time 型のデータの場合、ADO.NET 接続マネージャーでは、パラメーターの型が Input または Output で、データ型が string のパラメーターにこのデータを格納する必要があります。
datetimeoffset 型のデータの場合、ADO.NET 接続マネージャーでは、次のいずれかのパラメーターにこのデータを格納する必要があります。
- パラメーターの型が Input で、データ型が string のパラメーター。
- パラメーターの型が Output または ReturnValue で、データ型が datetimeoffset、string、または datetime2 のパラメーター。データ型が string または datetime2 のパラメーターを選択した場合、Integration Services ではデータが string または datetime2 に変換されます。
ADO 接続マネージャーでは、time 型または datetimeoffset 型のデータを、パラメーターの型が Input または Output で、データ型が adVarWchar のパラメーターに格納する必要があります。

SQL Server データ型と、それらが Integration Services データ型にどのようにマップされるかの詳細については、「データ型 (Transact-SQL)」および「Integration Services のデータ型」を参照してください。

OLE DB 接続マネージャーでの日付と時刻のパラメーターの使用

OLE DB 接続マネージャーを使用する場合、SQL 実行タスクには、SQL Server データ型 (date、time、datetime、datetime2、および datetimeoffset) のデータに関して特定のストレージ要件があります。このデータは、次のいずれかの型のパラメーターに格納する必要があります。

NVARCHAR データ型の入力パラメーター。
次の表に示す、適切なデータ型の出力パラメーター。

Output パラメーターの型

Date データ型

DBDATE

date

DBTIME2

time

DBTIMESTAMP

datetime,datetime2

DBTIMESTAMPOFFSET

datetimeoffset

Output パラメーターの型	Date データ型
DBDATE	date
DBTIME2	time
DBTIMESTAMP	datetime,datetime2
DBTIMESTAMPOFFSET	datetimeoffset

データが適切な入力パラメーターまたは出力パラメーターに格納されないと、パッケージは失敗します。

ODBC 接続マネージャーでの日付と時刻のパラメーターの使用

ODBC 接続マネージャーを使用する場合、SQL 実行タスクには、SQL Server データ型 (date、time、datetime、datetime2、または datetimeoffset) のデータに関して特定のストレージ要件があります。このデータは、次のいずれかの型のパラメーターに格納する必要があります。

SQL_WVARCHAR データ型の input パラメーター。

次の表に示す、適切なデータ型の output パラメーター。

Output パラメーターの型	Date データ型
SQL_DATE	date
SQL_SS_TIME2	time
SQL_TYPE_TIMESTAMP - または - SQL_TIMESTAMP	datetime,datetime2
SQL_SS_TIMESTAMPOFFSET	datetimeoffset

データが適切な入力パラメーターまたは出力パラメーターに格納されないと、パッケージは失敗します。

WHERE 句でのパラメーターの使用

SELECT、INSERT、UPDATE、および DELETE コマンドには、多くの場合、WHERE 句が含まれています。WHERE 句は、SQL コマンドを限定するために、ソーステーブル内の各行が満たすべき条件を定義したフィルターの役割を果たします。パラメーターは、WHERE 句で使用されるフィルター値を提供します。

パラメーターマーカーを使用して、パラメーター値を動的に指定できます。 SQL ステートメントで使用できるパラメーターマーカーとパラメーター名に関する規則は、SQL 実行タスクで使用される接続マネージャーの種類によって異なります。

次の表に、SELECT コマンドの例を接続マネージャーの種類別に示します。 INSERT、UPDATE、および DELETE ステートメントでも同様です。この例では、SELECT を使用して、2 つのパラメーターで指定された値よりも ProductID の値が大きい製品と小さい製品を、 AdventureWorks2012 の Product テーブルから返します。

接続の種類	SELECT 構文
EXCEL、ODBC、OLEDB	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET	SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

この例では、次の名前のパラメーターが必要になります。

EXCEL 接続マネージャーと OLE DB 接続マネージャーでは、パラメーター名 0 と 1 を使用します。 ODBC 接続では、1 と 2 を使用します。
ADO 接続では、Param1 や Param2 など、任意の 2 つのパラメーター名を使用します。ただし、これらのパラメーター名は、パラメーターリストの序数位置によってマップされる必要があります。
ADO.NET 接続では、パラメーター名 @parmMinProductID と @parmMaxProductID を使用します。

ストアドプロシージャでのパラメーターの使用

ストアドプロシージャを実行する SQL コマンドでは、パラメーターマッピングを使用することもできます。パラメーターマーカーとパラメーター名の使用方法に関する規則は、パラメーター化クエリの規則と同様に、SQL 実行タスクで使用される接続マネージャーの種類によって異なります。

次の表に、EXEC コマンドの例を接続マネージャーの種類別に示します。この例では、 AdventureWorks2012 の uspGetBillOfMaterials ストアドプロシージャを実行します。このストアドプロシージャでは、input パラメーター @StartProductID と @CheckDate を使用します。

接続の種類	EXEC 構文
EXCEL および OLEDB	EXEC uspGetBillOfMaterials ?, ?
ODBC	{call uspGetBillOfMaterials(?, ?)} ODBC の呼び出し構文の詳細については、MSDN ライブラリの ODBC プログラマリファレンスにある「プロシージャのパラメーター」を参照してください。
ADO	IsQueryStoredProcedure が False に設定されている場合は EXEC uspGetBillOfMaterials ?, ? IsQueryStoredProcedure が True に設定されている場合は uspGetBillOfMaterials
ADO.NET	IsQueryStoredProcedure が False に設定されている場合は EXEC uspGetBillOfMaterials @StartProductID, @CheckDate IsQueryStoredProcedure が True に設定されている場合は uspGetBillOfMaterials

出力パラメーターを使用するには、構文で各パラメーターマーカーの後に OUTPUT キーワードを指定する必要があります。たとえば、EXEC myStoredProcedure ? OUTPUT という出力パラメーターの構文は正しい構文です。

Transact-SQL ストアドプロシージャでの入力パラメーターと出力パラメーターの使用の詳細については、「EXECUTE (Transact-SQL)」を参照してください。

リターンコードの値の取得

ストアドプロシージャは、リターンコードという整数値を返してプロシージャの実行状態を表すことができます。 SQL 実行タスクにリターンコードを実装するには、ReturnValue 型のパラメーターを使用します。

次の表に、リターンコードを実装する EXEC コマンドの一部の例を接続の種類別に示します。すべての例で、input パラメーターを使用します。パラメーターマーカーとパラメーター名の使用方法に関する規則は、すべてのパラメーター型 (Input、Output、および ReturnValue) に適用される規則と同じです。

一部の構文では、パラメーターのリテラルがサポートされません。その場合は、変数を使用してパラメーター値を指定する必要があります。

接続の種類	EXEC 構文
EXCEL および OLEDB	EXEC ? = myStoredProcedure 1
ODBC	{? = call myStoredProcedure(1)} ODBC の呼び出し構文の詳細については、MSDN ライブラリの ODBC プログラマリファレンスにある「プロシージャのパラメーター」を参照してください。
ADO	IsQueryStoreProcedure が False に設定されている場合は EXEC ? = myStoredProcedure 1 IsQueryStoreProcedure が True に設定されている場合は myStoredProcedure
ADO.NET	IsQueryStoreProcedure が True に設定されている場合は myStoredProcedure

前の表に示した構文では、SQL 実行タスクは [直接入力] ソースタイプを使用してストアドプロシージャを実行します。 SQL 実行タスクは [ファイル接続] ソースタイプを使用してストアドプロシージャを実行することもできます。 SQL 実行タスクで [直接入力] または [ファイル接続] のどちらのソースタイプを使用するかに関係なく、ReturnValue 型のパラメーターを使用してリターンコードを実装します。 SQL 実行タスクで実行される SQL ステートメントのソースタイプの構成方法の詳細については、「[SQL 実行タスクエディター] ([全般] タブ)」を参照してください。

Transact-SQL ストアドプロシージャでのリターンコードの使用の詳細については、「RETURN (Transact-SQL)」を参照してください。