sp_describe_undeclared_parameters (Transact-SQL)
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウス
Transact-SQL バッチ内の宣言されていないパラメーターに関するメタデータを含む結果セットを返します。 @tsql バッチで使用されているが、@params で宣言されていない各パラメーターを考慮します。 返される結果セットには、そのような各パラメーターにつき 1 行が、その推定される型情報と共に含まれます。 @tsql 入力バッチが @params で宣言されているパラメーター以外にパラメーターを持たない場合、このプロシージャは空の結果セットを返します。
構文
sp_describe_undeclared_parameters
[ @tsql = ] 'Transact-SQL_batch'
[ , [ @params = ] N'parameters' data type ] [, ...n]
Note
専用 SQL プール内の Azure Synapse Analytics でこのストアド プロシージャを使用するには、データベース互換性レベルを 20 以上に設定します。 オプトアウトするには、データベース互換性レベルを 10 に変更します。
引数
[ @tsql = ] 'Transact-SQL\_batch' 1 つ以上の Transact-SQL ステートメント。 Transact-SQL_batch には、nvarchar(n) または nvarchar(max) を指定できます。
[ @params = ] N'parameters'` @params は、sp_executesql と同様に、Transact-SQL バッチのパラメーターの宣言文字列を提供します。 Parameters には、nvarchar(n) または nvarchar(max) を指定できます。
Transact-SQL_batch に埋め込まれたすべてのパラメーターの定義が含まれている 1 つの文字列を指定します。 この文字列は Unicode 定数または Unicode 変数にする必要があります。 各パラメーター定義は、パラメーター名とデータ型で構成されます。 n は、追加のパラメーター定義を示すプレースホルダーです。 Transact-SQL ステートメントまたはステートメント内のバッチにパラメーターが含まれていない場合、@params は不要です。 このパラメーターの既定値は NULL です。
DataType
パラメーターのデータ型です。
リターン コードの値
sp_describe_undeclared_parameters は成功時に常に 0 のリターン状態を返します。 プロシージャが RPC として呼び出されていて、エラーをスローした場合、リターン状態には sys.dm_exec_describe_first_result_set の error_type 列に記述されているエラーの種類が設定されます。 プロシージャが Transact-SQL から呼び出されると、エラーの場合でも、戻り値は常にゼロになります。
結果セット
sp_describe_undeclared_parameters は以下の結果セットを返します。
| 列名 | データ型 | 説明 |
|---|---|---|
| parameter_ordinal | int NOT NULL | 結果セット内のパラメーターの位置を示す序数を格納します。 最初のパラメーターの位置は 1 で指定されます。 |
| name | sysname NOT NULL | パラメーターの名前を格納します。 |
| suggested_system_type_id | int NOT NULL | sys.types で指定されたパラメーターのデータ型の system_type_id を格納します。 CLR 型の場合は、system_type_name 列が NULL を返しても、この列は値 240 を返します。 |
| suggested_system_type_name | nvarchar (256) NULL | データ型の名前を格納します。 パラメーターのデータ型に指定されている引数 (長さ、有効桁数、小数点以下桁数など) を含みます。 データ型がユーザー定義の別名型の場合は、基になるシステム型がここで指定されます。 CLR ユーザー定義データ型の場合は、この列には NULL が返されます。 パラメーターの型を推測できない場合は、NULL が返されます。 |
| suggested_max_length | smallint NOT NULL | 「sys.columns」を参照してください (max_length 列の詳細について)。 |
| suggested_precision | tinyint NOT NULL | 「sys.columns」を参照してください (precision 列の詳細について)。 |
| suggested_scale | tinyint NOT NULL | 「sys.columns」を参照してください (scale 列の詳細について)。 |
| suggested_user_type_id | int NULL | CLR 型と別名型の場合、sys.types で指定された列のデータ型の user_type_id を格納します。 それ以外の場合は NULL です。 |
| suggested_user_type_database | sysname NULL | CLR 型と別名型の場合、その型が定義されたデータベースの名前を格納します。 それ以外の場合は NULL です。 |
| suggested_user_type_schema | sysname NULL | CLR 型と別名型の場合、その型が定義されたスキーマの名前を格納します。 それ以外の場合は NULL です。 |
| suggested_user_type_name | sysname NULL | CLR 型と別名型の場合、その型の名前を格納します。 それ以外の場合は NULL です。 |
| suggested_assembly_qualified_type_name | nvarchar (4000) NULL | CLR 型の場合、その型を定義するアセンブリの名前とクラスを返します。 それ以外の場合は NULL です。 |
| suggested_xml_collection_id | int NULL | sys.columns で指定されたパラメーターのデータ型の xml_collection_id を格納します。 この列は、返される型が XML スキーマ コレクションに関連付けられていない場合は NULL を返します。 |
| suggested_xml_collection_database | sysname NULL | この型に関連付けられている XML スキーマ コレクションが定義されているデータベースを格納します。 この列は、返される型が XML スキーマ コレクションに関連付けられていない場合は NULL を返します。 |
| suggested_xml_collection_schema | sysname NULL | この型に関連付けられている XML スキーマ コレクションが定義されているスキーマを格納します。 この列は、返される型が XML スキーマ コレクションに関連付けられていない場合は NULL を返します。 |
| suggested_xml_collection_name | sysname NULL | この型に関連付けられている XML スキーマ コレクションの名前を格納します。 この列は、返される型が XML スキーマ コレクションに関連付けられていない場合は NULL を返します。 |
| suggested_is_xml_document | bit NOT NULL | 返されるデータ型が XML で、その型が XML ドキュメントであると保証される場合、1 を返します。 それ以外の場合は 0 を返します。 |
| suggested_is_case_sensitive | bit NOT NULL | この列が大文字と小文字を区別する文字列型の場合は 1、それ以外の場合は 0 を返します。 |
| suggested_is_fixed_length_clr_type | bit NOT NULL | この列が固定長の CLR 型の場合は 1、それ以外の場合は 0 を返します。 |
| suggested_is_input | bit NOT NULL | パラメーターが代入の左側以外で使用されている場合は、1 を返します。 それ以外の場合は 0 を返します。 |
| suggested_is_output | bit NOT NULL | パラメーターが代入の左側で使用される場合、つまりストアド プロシージャの出力パラメーターに渡される場合は、1 を返します。 それ以外の場合は 0 を返します。 |
| formal_parameter_name | sysname NULL | パラメーターがストアド プロシージャまたはユーザー定義関数の引数の場合は、対応する仮パラメーターの名前を返します。 それ以外の場合は NULL を返します。 |
| suggested_tds_type_id | int NOT NULL | 内部使用です。 |
| suggested_tds_length | int NOT 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) が特定されたので、型推論アルゴリズムは @p のデータ型を次の 2 つの方法のいずれかで推論します。
単純な推論
E(@p) = @p であり TT(@p) が存在する場合、つまり、@p がステップ 2 の最初に示した式のいずれかに対する直接の引数である場合、型推論アルゴリズムによって、@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) - その他の数値データ型または decimal データ型は考慮されません。
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 つを選択します。
E(@p) での暗黙の変換により一番小さい数を生成するデータ型選択されます。 特定のデータ型によって生成される E(@p) のデータ型が TT(@p) と異なる場合、型推論アルゴリズムは、データ型 E(@p) から TT(@p) へのもう 1 つの暗黙的な変換であると見なします。
次に例を示します。
SELECT * FROM t WHERE Col_Int = Col_Int + @pこの場合、E(@p) は Col_Int + @p であり、TT(@p) は int です。暗黙的な変換が生成されないため、@p には int が選択されます。 その他いずれかのデータ型を選択すると、少なくとも 1 つの暗黙的な変換が生成されます。
変換された最小数に複数のデータ型が結び付いている場合、優先順位の高いデータ型が使用されます。 次に例を示します。
SELECT * FROM t WHERE Col_Int = Col_smallint + @pこのケースで、int と smallint は 1 つの変換を生成します。 他のすべてのデータ型では、2 つ以上の変換が生成されます。 int は smallint よりも優先されるため、int が @p に使用されます。 データ型の優先順位の詳細については、「データ型の優先順位 (Transact-SQL)」を参照してください。
このルールは、ルール 1 に該当するすべてのデータ型と、最高位の優先順位を持つデータ型との間に暗黙的な変換がある場合にのみ適用されます。 暗黙的な変換がない場合、データ型の推論はエラーで失敗します。 たとえば、クエリ
SELECT @p FROM tでは、@p のすべてのデータ型が等しく適切であるため、データ型の推論は失敗します。 また、int から xml への暗黙的な変換は存在しません。たとえば、varchar(8000) と varchar(max) のように、2 つの類似したデータ型がルール 1 によって関連付けられる場合、小さい方のデータ型 (varchar(8000)) が選択されます。 同じ原則がnvarchar データ型と varbinary データ型にも適用されます。
ルール 1 のために、型推論アルゴリズムでは、特定の変換が他の変換よりも優先されます。 変換の優先順序は、次のとおりです。
長さが異なる同じ基本データ型間の変換。
同じデータ型の固定長バージョンと可変長バージョンの間の変換 (char から varchar など)。
NULL と int の間の変換。
その他の変換。
たとえば、クエリ 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 + @p の場合は、(c) の変換に該当するため、@p には int が選択されます。
アクセス許可
引数 @tsql を実行する権限が必要です。
例
次の例は、宣言されていない @id パラメーターおよび @name パラメーターに対して予期されるデータ型などの情報を返します。
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 パラメーターのみが記述されます。
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'
参照
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示