sp_describe_undeclared_parameters (Transact-SQL)

適用対象: SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Microsoft Fabric のツールハウス内の SQL 分析エンドポイント

Transact-SQL バッチ内の宣言されていないパラメーターに関するメタデータを含む結果セットを返します。 @tsql バッチで使用されているが、@params で宣言されていない各パラメーターを考慮します。 返される結果セットには、そのような各パラメーターにつき 1 行が、その推定される型情報と共に含まれます。 @tsql 入力バッチが @params で宣言されているパラメーター以外にパラメーターを持たない場合、このプロシージャは空の結果セットを返します。

Transact-SQL 構文表記規則

構文

sp_describe_undeclared_parameters
    [ @tsql = ] 'Transact-SQL_batch'
    [ , [ @params = ] N'params [ , ...n ]' ]

Note

専用 SQL プールの Azure Synapse Analytics でこのストアド プロシージャを使用するには、データベース互換性レベルを 20 以上に設定します。 オプトアウトするには、データベース互換性レベルを 10に変更します。

引数

[ @tsql = ] 'tsql'

1 つ以上の Transact-SQL ステートメント。 @tsql は、 nvarchar(n) または nvarchar(max)です。

[ @params = ] N'params'

@params は、 sp_executesql の動作と同様に、Transact-SQL バッチのパラメーターの宣言文字列を提供します。 @params は、 nvarchar(n) または nvarchar(max)です。

@tsqlに埋め込まれるすべてのパラメーターの定義を含む文字列。 この文字列は Unicode 定数または Unicode 変数にする必要があります。 各パラメーター定義は、パラメーター名とデータ型で構成されます。 n は、追加のパラメーター定義を示すプレースホルダーです。 Transact-SQL ステートメントまたはステートメント内のバッチにパラメーターが含まれていない場合、 @params は必要ありません。 このパラメーターの既定値は NULL です。

リターン コードの値

sp_describe_undeclared_parameters 成功すると常に 0 の状態が返されます。 プロシージャがエラーをスローし、プロシージャが RPC として呼び出された場合、sys.dm_exec_describe_first_result_setのerror_type列で説明されているように、エラーの種類によって戻り状態が設定されます。 プロシージャが Transact-SQL から呼び出されると、エラーの場合でも、戻り値は常にゼロになります。

結果セット

sp_describe_undeclared_parameters は以下の結果セットを返します。

列名	データ型	説明
parameter_ordinal	int	結果セット内のパラメーターの位置を示す序数を格納します。 最初のパラメーターの位置は、 1として指定されます。 NULL 値は許可されません。
name	sysname	パラメーターの名前を格納します。 NULL 値は許可されません。
suggested_system_type_id	int	sys.typesで指定されたパラメーターのデータ型のsystem_type_idを格納します。 CLR 型の場合、 system_type_name 列は NULLを返しますが、この列は 240値を返します。 NULL 値は許可されません。
suggested_system_type_name	nvarchar (256)	データ型の名前を格納します。 パラメーターのデータ型に指定されている引数 (長さ、有効桁数、小数点以下桁数など) を含みます。 データ型がユーザー定義の別名型の場合は、基になるシステム型がここで指定されます。 CLR ユーザー定義データ型の場合は、この列に NULL が返されます。 パラメーターの型を推測できない場合は、 NULL が返されます。 Null 許容。
suggested_max_length	smallint	以下を参照してください。sys.columns 列の説明 max_length 。 NULL 値は許可されません。
suggested_precision	tinyint	以下を参照してください。sys.columns (precision 列の詳細について)。 NULL 値は許可されません。
suggested_scale	tinyint	以下を参照してください。sys.columns (scale 列の詳細について)。 NULL 値は許可されません。
suggested_user_type_id	int	CLR 型とエイリアス型の場合、sys.typesで指定された列のデータ型のuser_type_idが含まれます。 それ以外の場合は NULL。 Null 許容。
suggested_user_type_database	sysname	CLR 型と別名型の場合、その型が定義されたデータベースの名前を格納します。 それ以外の場合は NULL。 Null 許容。
suggested_user_type_schema	sysname	CLR 型と別名型の場合、その型が定義されたスキーマの名前を格納します。 それ以外の場合は NULL。 Null 許容。
suggested_user_type_name	sysname	CLR 型と別名型の場合、その型の名前を格納します。 それ以外の場合は NULL。
suggested_assembly_qualified_type_name	nvarchar (4000)	CLR 型の場合、その型を定義するアセンブリの名前とクラスを返します。 それ以外の場合は NULL。 Null 許容。
suggested_xml_collection_id	int	sys.columnsで指定されたパラメーターのデータ型のxml_collection_idを格納します。 返される型が XML スキーマ コレクションに関連付けられていない場合、この列は NULL を返します。 Null 許容。
suggested_xml_collection_database	sysname	この型に関連付けられている XML スキーマ コレクションが定義されているデータベースを格納します。 返される型が XML スキーマ コレクションに関連付けられていない場合、この列は NULL を返します。 Null 許容。
suggested_xml_collection_schema	sysname	この型に関連付けられている XML スキーマ コレクションが定義されているスキーマを格納します。 返される型が XML スキーマ コレクションに関連付けられていない場合、この列は NULL を返します。 Null 許容。
suggested_xml_collection_name	sysname	この型に関連付けられている XML スキーマ コレクションの名前を格納します。 返される型が XML スキーマ コレクションに関連付けられていない場合、この列は NULL を返します。 Null 許容。
suggested_is_xml_document	bit	返される型が XML であり、その型が XML ドキュメントであることが保証されている場合は、 1 を返します。 それ以外の場合は、0 を返します。 NULL 値は許可されません。
suggested_is_case_sensitive	bit	列が大文字と小文字を区別する文字列型の場合は 1 を返し、大文字と小文字が区別されない場合は 0 を返します。 NULL 値は許可されません。
suggested_is_fixed_length_clr_type	bit	列が固定長 CLR 型の場合は 1 を返し、そうでない場合は 0 を返します。 NULL 値は許可されません。
suggested_is_input	bit	パラメーターが代入の左側以外の場所で使用されている場合は、 1 を返します。 それ以外の場合は、0 を返します。 NULL 値は許可されません。
suggested_is_output	bit	パラメーターが割り当ての左側で使用されるか、ストアド プロシージャの出力パラメーターに渡された場合に、 1 を返します。 それ以外の場合は、0 を返します。 NULL 値は許可されません。
formal_parameter_name	sysname	パラメーターがストアド プロシージャまたはユーザー定義関数の引数の場合は、対応する仮パラメーターの名前を返します。 それ以外の場合は、NULL を返します。 Null 許容。
suggested_tds_type_id	int	内部使用のみ。 NULL 値は許可されません。
suggested_tds_length	int	内部使用のみ。 NULL 値は許可されません。

解説

sp_describe_undeclared_parameters は常に 0 の状態を返します。

最も一般的な使用方法は、パラメーターを含み、それらのパラメーターを任意の方法で処理する必要がある Transact-SQL ステートメントがアプリケーションで指定される場合です。 たとえば、ユーザーが ODBC パラメーター構文を使用してクエリを提供するユーザー インターフェイス ( ODBCTest や RowsetViewerなど) です。 アプリケーションでは、パラメーターの数を動的に検出し、各パラメーターの入力をユーザーに求める必要があります。

別の例として、ユーザー入力なしで、アプリケーションがパラメーターをループして、そのデータを他の場所 (テーブルなど) から取得する必要がある場合が挙げられます。 この場合、アプリケーションはすべてのパラメーター情報を一度に渡す必要はありません。 代わりに、アプリケーションはプロバイダーからすべてのパラメーター情報を取得し、データそのものはテーブルから取得できます。 sp_describe_undeclared_parametersを使用するコードはより汎用的であり、データ構造が後で変更された場合に変更を必要とする可能性は低くなります。

sp_describe_undeclared_parameters は、次のいずれかの場合にエラーを返します。

• 入力 @tsql は、有効な Transact-SQL バッチではありません。 有効性は、Transact-SQL バッチの解析と分析によって決定されます。 クエリの最適化中または実行中にバッチによって発生したエラーは、Transact-SQL バッチが有効かどうかを判断する際には考慮されません。

• @params は NULL ではなく、パラメーターの構文的に有効な宣言文字列ではない文字列を含むか、パラメーターを複数回宣言する文字列が含まれている場合です。

• 入力 Transact-SQL バッチは、 @paramsで宣言されたパラメーターと同じ名前のローカル変数を宣言します。

• ステートメントは一時テーブルを参照します。

• 後でクエリ実行の対象となる永続的なテーブルの作成が、クエリに含まれています。

@tsql@paramsで宣言されたパラメーター以外のパラメーターがない場合、プロシージャは空の結果セットを返します。

Note

スカラー Transact-SQL 変数として変数を宣言する必要があります。または、エラーが表示されます。

パラメーター選択アルゴリズム

宣言されていないパラメーターを含むクエリの場合、宣言されていないパラメーターのデータ型の推論は 3 つのステップで行われます。

手順 1: サブ式のデータ型を検索する

宣言されていないパラメーターを持つクエリのデータ型推論の最初の手順は、宣言されていないパラメーターに依存しないデータ型を持つすべてのサブ式のデータ型を見つけることです。 次の式については型を決定できます。

• 列、定数、変数、および宣言されたパラメーター。
• ユーザー定義関数 (UDF) の呼び出しの結果。
• すべての入力の宣言されていないパラメーターに依存しないデータ型を持つ式。

たとえば、クエリ SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2 を考えます。 式 dbo.tbl(@p1) + c1 と c2 にはデータ型があり、式の @p1 と @p2 + 2 はありません。

この手順の実行後、UDF の呼び出し以外の任意の式に、データ型のない 2 つの引数が含まれている場合、型推論はエラーで失敗します。 たとえば、次のすべての式ではエラーが発生します。

SELECT * FROM t1 WHERE @p1 = @p2;
SELECT * FROM t1 WHERE c1 = @p1 + @p2;
SELECT * FROM t1 WHERE @p1 = SUBSTRING(@p2, 2, 3);

次の例では、エラーは生成されません。

SELECT * FROM t1 WHERE @p1 = dbo.tbl(c1, @p2, @p3);

手順 2: 最も内側の式を検索する

指定された宣言されていないパラメーター @pの場合、型推論アルゴリズムは、@pを含み、次のいずれかの引数である最も内側の式E(@p)を検索します。

• 比較演算子または代入演算子の引数。
• ユーザー定義関数 (テーブル値 UDF を含む)、プロシージャ、またはメソッドの引数。
• INSERT ステートメントのVALUES句の引数。
• CASTまたはCONVERTの引数。

型推論アルゴリズムは、E(@p)のターゲット データ型TT(@p)を検索します。 前の例のターゲット データ型は次のとおりです。

• 比較または代入の反対側のデータ型。
• この引数が渡されるパラメーターの宣言されたデータ型。
• この値が挿入される列のデータ型。
• ステートメントがキャストまたは変換するデータ型。

たとえば、クエリ SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1) を考えます。 E(@p1) = @p1、E(@p2) = @p2 + c1、TT(@p1)はdbo.tblの宣言された戻り値のデータ型であり、TT(@p2)はdbo.tblの宣言されたパラメーター データ型です。

@pが手順 2. の最初に示した式に含まれていない場合、型推論アルゴリズムは、E(@p)が@pを含む最大のスカラー式であると判断し、型推論アルゴリズムはE(@p)のターゲット データ型TT(@p)を計算しません。 たとえば、クエリが SELECT @p + 2 されている場合、 E(@p) = @p + 2され、 TT(@p)はありません。

手順 3: データ型を推測する

E(@p)とTT(@p)が識別されたので、型推論アルゴリズムは、次の 2 つの方法のいずれかで@pのデータ型を推定します。

• 単純な推論

  E(@p) = @pとTT(@p)が存在する場合、つまり、@pが手順 2 の最初に示した式の 1 つに直接引数である場合、型推論アルゴリズムは@pのデータ型をTT(@p)する必要があります。 次に例を示します。

  SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3);
  

  @p1、@p2、および@p3のデータ型は、それぞれc1のデータ型、dbo.tblの戻り値のデータ型、およびdbo.tblのパラメーター データ型になります。

  特殊なケースとして、 @p が <、 >、 <=、または >= 演算子の引数である場合、単純な控除ルールは適用されません。 型推論アルゴリズムでは、次のセクションで説明する一般的な推論ルールが使用されます。 たとえば、 c1 がデータ型 char(30)の列である場合は、次の 2 つのクエリについて考えてみましょう。

  SELECT * FROM t WHERE c1 = @p;
  SELECT * FROM t WHERE c1 > @p;
  

  最初のケースでは、型推論アルゴリズムは、この記事の前半の規則に従って、@pのデータ型としてchar(30)を推定します。 2 つ目のケースでは、型推論アルゴリズムは次のセクションの一般的な推論ルールに従って、データ型が varchar(8000) であると推論します。

• 一般的な推論

  単純な推論が適用されない場合は、宣言されていないパラメーターに対して次のデータ型が考慮されます。

  • 整数データ型 (bit、tinyint、smallint、int、bigint)

  • 通貨データ型 (smallmoney、money)

  • 浮動小数点データ型 (float、real)

  • numeric(38, 19) - その他の数値または 10 進数のデータ型は考慮されません。

  • varchar(8000)、varchar(max)、nvarchar(4000)、および nvarchar(max) - 他の文字列データ型 (text、char(8000)、nvarchar(30) など) は考慮されません。

  • varbinary(8000) と varbinary(max) - その他のバイナリ データ型は考慮されません ( image、 binary(8000)、 varbinary(30) など)。

  • date、 time(7)、 smalldatetime、 datetime、 datetime2(7)、 datetimeoffset(7) - time(4)など、その他の日付と時刻の種類は考慮されません。

  • sql_variant

  • xml

  • CLR システム定義データ型 (hierarchyid、geometry、geography)

  • CLR ユーザー定義型

選択条件

候補のデータ型のうち、クエリを無効にするデータ型は拒否されます。 型推論アルゴリズムは、残っている候補のデータ型から次のルールに従って 1 つを選択します。

1. E(@p)で暗黙的な変換の最小数を生成するデータ型が選択されます。 特定のデータ型がTT(@p)とは異なるE(@p)のデータ型を生成する場合、型推論アルゴリズムでは、これをE(@p)のデータ型からTT(@p)への余分な暗黙的な変換と見なします。

   次に例を示します。

   SELECT * FROM t WHERE Col_Int = Col_Int + @p;
   

   この場合、 E(@p) は Col_Int + @p され、 TT(@p) は int です。 int は暗黙的な変換を生成しないため、 @p に対して選択されます。 その他いずれかのデータ型を選択すると、少なくとも 1 つの暗黙的な変換が生成されます。

2. 変換された最小数に複数のデータ型が結び付いている場合、優先順位の高いデータ型が使用されます。 次に例を示します。

   SELECT * FROM t WHERE Col_Int = Col_smallint + @p;
   

   このケースで、int と smallint は 1 つの変換を生成します。 他のすべてのデータ型では、2 つ以上の変換が生成されます。 intはsmallintよりも優先されるため、@pにはintが使用されます。 データ型の優先順位の詳細については、「 Data 型の優先順位」を参照してください。

   このルールは、ルール 1 に従って関連付けるすべてのデータ型と、最も優先順位の高いデータ型の間に暗黙的な変換がある場合にのみ適用されます。 暗黙的な変換がない場合、データ型の推論はエラーで失敗します。 たとえば、クエリ SELECT @p FROM tでは、 @p のすべてのデータ型が同じように適切であるため、データ型の推論は失敗します。 たとえば、 int から xml への暗黙的な変換はありません。

3. たとえば、varchar(8000) と varchar(max) のように、2 つの類似したデータ型がルール 1 によって関連付けられる場合、小さい方のデータ型 (varchar(8000)) が選択されます。 同じ原則がnvarchar データ型と varbinary データ型にも適用されます。

4. ルール 1 のために、型推論アルゴリズムでは、特定の変換が他の変換よりも優先されます。 変換の優先順序は、次のとおりです。

   1. 長さが異なる同じ基本データ型間の変換。
   2. 同じデータ型の固定長バージョンと可変長バージョンの間の変換 (たとえば、 char varchar)。
   3. NULL と int の間の変換。
   4. その他の変換。

たとえば、クエリ SELECT * FROM t WHERE [Col_varchar(30)] > @p の場合、変換 (a) が最適であるため varchar(8000) が選択されます。 クエリ SELECT * FROM t WHERE [Col_char(30)] > @p の場合も varchar(8000) が選択されます。これによって変換 (b) が生成され、別の選択肢 (varchar(4000) など) で変換 (d) が生成されるためです。

最後の例として、クエリのSELECT NULL + @pint型 (c) 変換が行われるため、@pに対して選択されます。

アクセス許可

@tsql引数を実行するためのアクセス許可が必要です。

例

次の例は、宣言されていない @id パラメーターおよび @name パラメーターに対して予期されるデータ型などの情報を返します。

EXEC sp_describe_undeclared_parameters @tsql =
N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR name = @name';

@id パラメーターが @params 参照として指定された場合は、@id パラメーターは結果セットから省略され、@name パラメーターのみが記述されます。

EXEC sp_describe_undeclared_parameters @tsql =
N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name',
@params = N'@id int';

関連するコンテンツ

• sp_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)