Freigeben über

sp_describe_undeclared_parameters (Transact-SQL)

  • Artikel

Gibt ein Resultset zurück, das Metadaten zu nicht deklarierten Parametern in einem Transact-SQL-Batch enthält. Dabei wird jeder Parameter berücksichtigt, der im @tsql-Batch verwendet wird, aber nicht in @params deklariert ist. Es wird ein Resultset zurückgegeben, das eine Zeile für jeden dieser Parameter und die abgeleiteten Typinformationen für diesen Parameter enthält. Wenn der @tsql-Eingabebatch nur über Parameter verfügt, die in @params deklariert wurden, gibt die Prozedur ein leeres Resultset zurück.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

  • [ @tsql = ] 'Transact-SQL_batch'
    Eine oder mehrere Transact-SQL-Anweisungen. Transact-SQL_batch kann vom Typ nvarchar(n) oder nvarchar(max) sein.

  • [ @params = ] N'parameters'
    @params stellt ähnlich der Funktionsweise von sp_executesql eine Deklarationszeichenfolge für Parameter für den Transact-SQL-Batch bereit. Parameters kann vom Typ nvarchar(n) oder nvarchar(max) sein.

    Eine Zeichenfolge, die die Definitionen aller Parameter enthält, die in Transact-SQL_batch eingebettet wurden. Die Zeichenfolge muss eine Unicode-Konstante oder eine Unicode-Variable sein. Jede Parameterdefinition besteht aus einem Parameternamen und einem Datentyp. Dabei ist n ein Platzhalter für zusätzlicher Parameterdefinitionen. Wenn die Transact-SQL-Anweisung oder der Batch in der Anweisung keine Parameter enthält, ist @params nicht erforderlich. Der Standardwert für diesen Parameter ist NULL.

  • Datatype
    Der Datentyp des Parameters.

Rückgabecodewerte

sp_describe_undeclared_parameters gibt bei Erfolg immer den Rückgabestatus 0 zurück. Wenn von der Prozedur ein Fehler ausgelöst und die Prozedur als RPC aufgerufen wird, wird der Rückgabestatus vom Fehlertyp aufgefüllt, wie in der error_type-Spalte von sys.dm_exec_describe_first_result_set beschrieben. Wenn die Prozedur von Transact-SQL aufgerufen wird, ist der Rückgabewert immer 0, auch bei Fehlern.

Resultsets

sp_describe_undeclared_parameters gibt das folgende Resultset zurück.

Spaltenname

Datentyp

Beschreibung

parameter_ordinal

int NOT NULL

Enthält die Ordnungsposition des Parameters im Resultset. Die Position des ersten Parameters wird mit 1 angegeben.

name

sysname NOT NULL

Enthält den Namen des Parameters.

suggested_system_type_id

int NOT NULL

Enthält die system_type_id des Datentyps für den Parameter, wie in sys.types angegeben.

Bei CLR-Typen wird von dieser Spalte der Wert 240 zurückgegeben, obwohl von der system_type_name-Spalte NULL zurückgegeben wird.

suggested_system_type_name

nvarchar (256) NULL

Enthält den Namen des Datentyps. Enthält für den Datentyp des Parameters angegebene Argumente (z. B. Länge, Genauigkeit, Skala). Wenn der Datentyp ein benutzerdefinierter Aliastyp ist, wird der zugrunde liegende Systemtyp hier angegeben. Wenn es sich um einen benutzerdefinierten CLR-Datentyp handelt, wird in dieser Spalte NULL zurückgegeben. Wenn der Typ des Parameters nicht abgeleitet werden kann, wird NULL zurückgegeben.

suggested_max_length

smallint NOT NULL

sys.columns enthält eine Beschreibung der Spalte max_length.

suggested_precision

tinyint NOT NULL

sys.columns enthält eine Beschreibung der Genauigkeitsspalte.

suggested_scale

tinyint NOT NULL

sys.columns enthält eine Beschreibung der Skalierungsspalte.

suggested_user_type_id

int NULL

Enthält bei CLR- und Aliastypen die user_type_id des Datentyps für die Spalte, wie in sys.types angegeben. Andernfalls NULL.

suggested_user_type_database

sysname NULL

Enthält bei CLR- und Aliastypen den Namen der Datenbank, in der der Typ definiert wurde. Andernfalls NULL.

suggested_user_type_schema

sysname NULL

Enthält bei CLR- und Aliastypen den Namen des Schemas, in dem der Typ definiert wurde. Andernfalls NULL.

suggested_user_type_name

sysname NULL

Enthält bei CLR- und Aliastypen den Namen des Typs. Andernfalls NULL.

suggested_assembly_qualified_type_name

nvarchar (4000) NULL

Gibt bei CLR-Typen den Namen der Assembly und der Klasse zurück, die den Typ definieren. Andernfalls NULL.

suggested_xml_collection_id

int NULL

Enthält die xml_collection_id des Datentyps für den Parameter, wie in sys.columns angegeben. Diese Spalte gibt NULL zurück, wenn der zurückgegebene Typ keiner XML-Schemaauflistung zugeordnet ist.

suggested_xml_collection_database

sysname NULL

Enthält die Datenbank, in der die XML-Schemaauflistung definiert ist, die diesem Typ zugeordnet wurde. Diese Spalte gibt NULL zurück, wenn der zurückgegebene Typ keiner XML-Schemaauflistung zugeordnet ist.

suggested_xml_collection_schema

sysname NULL

Enthält das Schema, in dem die XML-Schemaauflistung definiert ist, die diesem Typ zugeordnet wurde. Diese Spalte gibt NULL zurück, wenn der zurückgegebene Typ keiner XML-Schemaauflistung zugeordnet ist.

suggested_xml_collection_name

sysname NULL

Enthält den Namen der XML-Schemaauflistung, die diesem Typ zugeordnet wurde. Diese Spalte gibt NULL zurück, wenn der zurückgegebene Typ keiner XML-Schemaauflistung zugeordnet ist.

suggested_is_xml_document

bit NOT NULL

Gibt 1 zurück, wenn als Typ XML zurückgegeben wird und sichergestellt ist, dass es sich um ein XML-Dokument handelt. Andernfalls wird 0 zurückgegeben.

suggested_is_case_sensitive

bit NOT NULL

Gibt 1 zurück, wenn die Spalte von einem Zeichenfolgentyp ist, bei dem die Groß-/Kleinschreibung beachtet wird, andernfalls 0.

suggested_is_fixed_length_clr_type

bit NOT NULL

Gibt 1 zurück, wenn die Spalte von einem CLR-Typ mit fester Länge ist, andernfalls 0.

suggested_is_input

bit NOT NULL

Gibt 1 zurück, wenn der Parameter an anderer Stelle verwendet wird als links einer Zuweisung. Andernfalls wird 0 zurückgegeben.

suggested_is_output

bit NOT NULL

Gibt 1 zurück, wenn der Parameter auf der linken Seite einer Zuweisung verwendet wird oder an einen Ausgabeparameter einer gespeicherten Prozedur übergeben wird. Andernfalls wird 0 zurückgegeben.

formal_parameter_name

sysname NULL

Wenn es sich bei dem Parameter um ein Argument für eine gespeicherte Prozedur oder eine benutzerdefinierte Funktion handelt, wird der Name des entsprechenden formalen Parameters zurückgegeben. Andernfalls wird NULL zurückgegeben.

suggested_tds_type_id

int NOT NULL

Für die interne Verwendung.

suggested_tds_length

int NOT NULL

Für die interne Verwendung.

Hinweise

sp_describe_undeclared_parameters gibt immer den Rückgabestatus 0 zurück.

Die häufigste Verwendung besteht darin, dass für eine Anwendung eine Transact-SQL-Anweisung ausgeführt wird, die möglicherweise Parameter enthält und von dieser verarbeitet werden muss. Dies gilt beispielsweise für eine Benutzeroberfläche (z. B. ODBCTest oder RowsetViewer), bei der der Benutzer eine Abfrage mit ODBC-Parametersyntax eingibt. Die Anwendung muss die Anzahl der Parameter dynamisch ermitteln und bei jedem den Benutzer auffordern.

Ein weiteres Beispiel ist der Fall, in dem eine Anwendung ohne Benutzereingabe eine Schleife in den Parametern ausführen und die Daten für diese Parameter von einem anderen Speicherort (z. B. einer Tabelle) abrufen muss. In diesem Fall muss die Anwendung nicht alle Parameterinformationen zusammen übergeben. Stattdessen kann die Anwendung alle Parameterinformationen vom Anbieter und die Daten selbst aus der Tabelle abrufen. Code, in dem sp_describe_undeclared_parameters verwendet wird, ist generischer und muss bei späteren Änderungen der Datenstruktur weniger wahrscheinlich geändert werden.

sp_describe_undeclared_parameters gibt in einem der folgenden Fälle einen Fehler zurück.

  • Die @tsql-Eingabe ist kein gültiger Transact-SQL-Batch. Die Gültigkeit wird durch Analysieren des Transact-SQL-Batchs bestimmt. Fehler, die vom Batch im Rahmen der Abfrageoptimierung oder -ausführung ausgelöst werden, bleiben unberücksichtigt, wenn die Gültigkeit des Transact-SQL-Batchs überprüft wird.

  • @params ist nicht NULL und enthält eine Zeichenfolge, die keine syntaktisch gültige Deklarationszeichenfolge für Parameter darstellt, oder eine Zeichenfolge, die einen Parameter mehrmals deklariert.

  • Der Transact-SQL-Eingabebatch deklariert eine lokale Variable mit dem gleichen Namen wie ein in @params deklarierter Parameter.

  • Die Anweisung erstellt temporäre Tabellen.

Wenn @tsql über keine anderen Parameter verfügt als die in @params deklarierten Parameter, gibt die Prozedur ein leeres Resultset zurück.

Algorithmus für die Parameterauswahl

Bei einer Abfrage mit nicht deklarierten Parametern erfolgt die Datentypableitung für nicht deklarierte Parameter in drei Schritten.

Schritt 1

Der erste Schritt der Datentypableitung für eine Abfrage mit nicht deklarierten Parametern besteht darin, die Datentypen aller Teilausdrücke zu ermitteln, deren Datentypen nicht von den nicht deklarierten Parametern abhängen. Der Typ kann für die folgenden Ausdrücke ermittelt werden:

  • Spalten, Konstanten, Variablen und deklarierte Parameter.

  • Ergebnisse eines Aufrufs einer benutzerdefinierten Funktion (User-Defined Function, UDF).

  • Ein Ausdruck mit Datentypen, die nicht für alle Eingaben von den nicht deklarierten Parametern abhängen.

Betrachten Sie beispielsweise die folgende Abfrage: SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. Die Ausdrücke dbo.tbl(@p1) + c1 und c2 weisen Datentypen auf, die Ausdrücke @p1 und @p2 + 2 jedoch nicht.

Wenn nach diesem Schritt ein anderer Ausdruck als ein UDF-Aufruf über zwei Argumente ohne Datentypen verfügt, tritt bei der Typableitung ein Fehler auf. Beispielsweise führen alle folgenden Ausdrücke zu Fehlern:

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

Im folgenden Beispiel wird kein Fehler erzeugt:

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

Schritt 2

Für den angegebenen, nicht deklarierten Parameter @p sucht der Typableitungsalgorithmus den innersten Ausdruck E(@p), der @p enthält und von einem der folgenden Typen ist:

  • Ein Argument für einen Vergleich oder ein Zuweisungsoperator.

  • Ein Argument für eine benutzerdefinierte Funktion (einschließlich Tabellenwert-UDFs), Prozedur oder Methode.

  • Ein Argument für eine VALUES-Klausel einer INSERT-Anweisung.

  • Ein Argument für CAST oder CONVERT.

Der Typableitungsalgorithmus sucht für E(@p) den Zieldatentyp TT(@p). Für die vorherigen Beispiele sind die folgenden Zieldatentypen möglich:

  • Der Datentyp der anderen Seite des Vergleichs oder der Zuweisung.

  • Der deklarierte Datentyp des Parameters, an den dieses Argument übergeben wird.

  • Der Datentyp der Spalte, in der dieser Wert eingefügt wird.

  • Der Datentyp, in den die Anweisung umgewandelt oder konvertiert wird.

Betrachten Sie beispielsweise die folgende Abfrage: SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). E(@p1) = @p1, E(@p2) = @p2 + c1, TT(@p1) ist dann der deklarierte Rückgabedatentyp von dbo.tbl, und TT(@p2) ist der deklarierte Parameterdatentyp für dbo.tbl.

Wenn @p in keinem Ausdruck enthalten ist, der am Anfang des Schritts 2 aufgelistet wird, ermittelt der Typableitungsalgorithmus, dass E(@p) der größte Skalarausdruck ist, der @p enthält, und der Typableitungsalgorithmus berechnet für E(@p) nicht den Zieldatentyp TT(@p). Wenn die Abfrage z. B. SELECT @p + 2 lautet, dann ist E(@p) = @p + 2, und TT(@p) ist nicht vorhanden.

Schritt 3

Da E(@p) und TT(@p) nun identifiziert sind, leitet der Typableitungsalgorithmus mit einer der folgenden beiden Methoden einen Datentyp für @p ab:

  • Einfache Ableitung

    Wenn E(@p) = @p und TT(@p) vorhanden ist, d. h., @p ein direktes Argument für einen der Ausdrücke ist, die am Anfang des Schritts 2 aufgelistet werden, leitet der Typableitungsalgorithmus den Datentyp von @p als TT(@p) ab. Beispiel:

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

    Der Datentyp für @p1, @p2 und @p3 ist der Datentyp von c1, der Rückgabedatentyp von dbo.tbl bzw. der Parameterdatentyp für dbo.tbl.

    Ein Spezialfall liegt vor, wenn @p ein Argument für einen <-, >-, <=- oder >=-Operator ist. Hier gelten die Regeln für die einfache Ableitung nicht. Der Typableitungsalgorithmus verwendet die allgemeinen, im nächsten Abschnitt erklärten Ableitungsregeln. Betrachten Sie beispielsweise die folgenden beiden Abfragen für Fälle, in denen c1 eine Spalte vom Datentyp char(30) ist:

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

    Im ersten Fall leitet der Typableitungsalgorithmus anhand der zuvor in diesem Thema erläuterten Regeln als Datentyp für @p char(30) ab. Im zweiten Fall leitet der Typableitungsalgorithmus anhand der im nächsten Abschnitt erläuterten allgemeinen Ableitungsregeln varchar(8000) ab.

  • Allgemeine Ableitung

    Wenn keine einfache Ableitung möglich ist, kommen für nicht deklarierte Parameter die folgenden Datentypen infrage:

    • Integer-Datentypen (bit, tinyint, smallint, int, bigint)

    • Money-Datentypen (smallmoney, money)

    • Floating-point-Datentypen (float, real)

    • numeric(38, 19) – Andere numerische oder dezimale Datentypen kommen nicht infrage.

    • varchar(8000), varchar(max), nvarchar(4000) und nvarchar(max) – Andere string-Datentypen (z. B. text, char(8000), nvarchar(30) usw.) kommen nicht infrage.

    • varbinary(8000) und varbinary(max) – Andere binäre Datentypen (z. B. image, binary(8000), varbinary(30) usw.) kommen nicht infrage.

    • date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) – Andere Daten- und Uhrzeittypen, z. B. time(4), kommen nicht infrage.

    • sql_variant

    • xml

    • Systemdefinierte CLR-Typen (hierarchyid, geometry, geography)

    • Benutzerdefinierte CLR-Typen

Auswahlkriterien

Von den infrage kommenden Datentypen wird jeder Datentyp abgelehnt, durch den die Abfrage ungültig gemacht würde. Von den verbleibenden infrage kommenden Datentypen wählt der Typableitungsalgorithmus anhand der folgenden Regeln einen aus.

  1. Der Datentyp wird ausgewählt, der die kleinste Anzahl impliziter Konvertierungen in E(@p) erzeugt. Wenn ein bestimmter Datentyp einen Datentyp für E(@p) erzeugt, der sich von TT(@p) unterscheidet, betrachtet der Typableitungsalgorithmus dies als zusätzliche implizite Konvertierung aus dem Datentyp E(@p) nach TT(@p).

    Beispiel:

    SELECT * FROM t WHERE Col_Int = Col_Int + @p

    In diesem Fall ist E(@p) vom Typ Col_Int + @p und TT(@p) vom Typ int. Für @p wird int ausgewählt, da dieser Typ keine impliziten Konvertierungen erzeugt. Jeder andere ausgewählte Datentyp erzeugt mindestens eine implizite Konvertierung.

  2. Wenn mehrere Datentypen gleich wenige Konvertierungen erzeugen, wird der Datentyp mit dem höheren Rang verwendet. Beispiel:

    SELECT * FROM t WHERE Col_Int = Col_smallint + @p

    In diesem Fall erzeugen int und smallint eine Konvertierung. Jeder andere Datentyp erzeugt mehr als eine Konvertierung. Da int Vorrang gegenüber smallint besitzt, wird für @p der Typ int verwendet. Weitere Informationen zur Rangfolge von Datentypen finden Sie unter Rangfolge der Datentypen (Transact-SQL).

    Diese Regel gilt nur, wenn zwischen jedem Datentyp, der nach Regel 1 genauso wenige Konvertierungen wie ein anderer erzeugt, und dem Datentyp mit dem höchsten Rang eine implizite Konvertierung erfolgt. Wenn keine implizite Konvertierung erfolgt, tritt bei der Datentypableitung ein Fehler auf. In der Abfrage SELECT @p FROM t beispielsweise tritt bei der Datentypableitung ein Fehler auf, weil jeder Datentyp für @p gleich gut wäre. Beispielsweise erfolgt keine implizite Konvertierung von int nach xml.

  3. Wenn zwei ähnliche Datentypen unter Regel 1 gleich wenige Konvertierungen erzeugen, z. B. varchar(8000) und varchar(max), wird der kleinere Datentyp (varchar(8000)) ausgewählt. Das gleiche Prinzip gilt für die Datentypen nvarchar und varbinary.

  4. Für die Zwecke der Regel 1 bevorzugt der Typableitungsalgorithmus bestimmte Konvertierungen gegenüber anderen. Die Konvertierungen werden in der folgenden Reihenfolge bevorzugt (beste bis schlechteste):

    1. Konvertierung zwischen gleichem Basisdatentyp mit unterschiedlicher Länge.

    2. Konvertierung zwischen Versionen desselben Datentyps mit fester Länge und mit variabler Länge (z. B. char nach varchar).

    3. Konvertierung zwischen NULL und int.

    4. Jede andere Konvertierung.

Für die Abfrage SELECT * FROM t WHERE [Col_varchar(30)] > @p wird beispielsweise varchar(8000) ausgewählt, da die Konvertierung (a) die beste ist. Für die Abfrage SELECT * FROM t WHERE [Col_char(30)] > @p wird ebenfalls varchar(8000) ausgewählt, da dieser Datentyp eine Konvertierung vom Typ (b) verursacht und eine andere Option (z. B. varchar(4000)) eine Konvertierung vom Typ (d) verursachen würde.

Letztlich führt die Abfrage SELECT NULL + @p bei Auswahl von int für @p zu einer Konvertierung vom Typ (c).

Berechtigungen

Erfordert die Berechtigung zur Ausführung des @tsql-Arguments.

Beispiele

Im folgenden Beispiel werden bestimmte Informationen zurückgegeben, z. B. der erwartete Datentyp für den nicht deklarierten @id-Parameter und den nicht deklarierten @name-Parameter.

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

Wenn der @id-Parameter als @params-Verweis bereitgestellt wird, wird der @id-Parameter im Resultset ausgelassen, und nur der @name-Parameter wird beschrieben.

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'

Siehe auch

Verweis

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)