sp_describe_undeclared_parameters (Transact-SQL)

適用対象:Database Azure SQL Managed Instance Azure Synapse Analytics Azure SQL SQL Server (サポートされているすべてのバージョン)

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

Topic link iconTransact-SQL 構文表記規則

構文

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 バッチのパラメーターの宣言文字列を提供します。 パラメーター には 、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 から呼び出された場合、エラーの場合でも戻り値は常に 0 になります。

結果セット

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 を参照してください。 有効桁数列の説明を指定します。
suggested_scale tinyint NOT NULL sys.columns を参照してください。 スケール列の説明。
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 ビット NOT NULL 返される型が XML であり、その型が XML ドキュメントであることが保証されている場合は、1 を返します。 それ以外の場合は 0 を返します。
suggested_is_case_sensitive ビット NOT NULL この列が大文字と小文字を区別する文字列型の場合は 1、それ以外の場合は 0 を返します。
suggested_is_fixed_length_clr_type ビット NOT NULL この列が固定長の CLR 型の場合は 1、それ以外の場合は 0 を返します。
suggested_is_input ビット NOT NULL 割り当ての左側以外の場所でパラメーターを使用する場合は、1 を返します。 それ以外の場合は 0 を返します。
suggested_is_output ビット 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 で宣言されているパラメーター以外にパラメーターを持たない場合、このプロシージャは空の結果セットを返します。

注意

変数をスカラー TSQL 変数として宣言するか、エラーが表示されます。

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

宣言されていないパラメーターを持つクエリの場合、宣言されていないパラメーターのデータ型の推論は、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 の最初に示した式の 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) を推定します。

  • 一般的な控除

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

    • 整数データ型 (bittinyintsmallintintbigint)

    • Money データ型 (smallmoneymoney)

    • 浮動小数点データ型 (floatreal)

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

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

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

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

    • sql_variant

    • xml

    • CLR システム定義型 (hierarchyidgeometrygeography)

    • 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 です暗黙的な 変換が生成されないため、@pには int が選択されます。 他の任意のデータ型を選択すると、少なくとも 1 つの暗黙的な変換が生成されます。

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

    SELECT * FROM t WHERE Col_Int = Col_smallint + @p  
    

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

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

  3. 規則 1 で類似する 2 つのデータ型 (例: varchar(8000)varchar(max)) が結び付いている場合、小さいデータ型 (varchar(8000)) が選択されます。 nvarchar データ型と varbinary データ型にも同じ原則が適用されます。

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

    1. 長さが異なる同じ基本データ型間の変換。

    2. 同じデータ型の固定長バージョンと可変長バージョンの間の変換 ( char から varchar など)。

    3. NULLint の間の変換。

    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 + @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'  
  

参照