sp_describe_undeclared_parameters (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsSQL Analytics-Endpunkt in Microsoft FabricWarehouse in Microsoft Fabric

Gibt ein Resultset zurück, das Metadaten zu nicht deklarierten Parametern in einem Transact-SQL-Batch enthält. Berücksichtigt jeden im @tsql-Batch verwendeten, aber nicht in @params deklarierten Parameter. Ein Resultset wird zurückgegeben, das für jeden dieser Parameter eine Zeile mit den 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.

Transact-SQL-Syntaxkonventionen

Syntax

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

Hinweis

Um diese gespeicherte Prozedur in Azure Synapse Analytics im dedizierten SQL-Pool zu verwenden, legen Sie den Datenbank-Kompatibilitätsgrad auf mindestens 20 fest. Um diese Option zu deaktivieren, ändern Sie den Datenbank-Kompatibilitätsgrad in 10.

Argumente

[ @tsql = ] 'Transact-SQL\_batch' Mindestens eine Transact-SQL-Anweisung. Transact-SQL_batch kann nvarchar(n) oder nvarchar(max) sein.

[ @params = ] N'parameters'` @params stellt eine Deklarationszeichenfolge für Parameter für den Transact-SQL-Batch bereit. Dies ähnelt der Funktionsweise von sp_executesql. Parameter können nvarchar(n) oder nvarchar(max) sein.

Eine Zeichenfolge, die die Definitionen aller in Transact-SQL_batch eingebetteten Parameter enthält. 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.

Datentyp
Der Datentyp des Parameters.

Rückgabecodewerte

sp_describe_undeclared_parameters gibt bei Erfolg immer den Rückgabestatus 0 zurück. Wenn die Prozedur einen Fehler auslö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 über Transact-SQL aufgerufen wird, lautet 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 die Spalte system_type_name NULL zurückgibt.
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 Informationen finden Sie unter „sys.columns“. enthält eine Beschreibung der Spalte max_length.
suggested_precision tinyint NOT NULL Informationen finden Sie unter „sys.columns“. enthält eine Beschreibung der Genauigkeitsspalte.
suggested_scale tinyint NOT NULL Informationen finden Sie unter „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 der 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 definiert. 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.

Bemerkungen

sp_describe_undeclared_parameters gibt immer einen Rückgabestatus von 0 zurück.

Im häufigsten Anwendungsfall erhält eine Anwendung eine Transact-SQL-Anweisung, die möglicherweise Parameter enthält, die verarbeitet werden müssen. 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 liegt vor, wenn eine Anwendung ohne Benutzereingabe eine Schleife in den Parametern ausführen und die Daten für diese 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 mit sp_describe_undeclared_parameters ist generischer und muss bei späteren Änderungen der Datenstruktur wahrscheinlich nicht geändert werden.

sp_describe_undeclared_parameters gibt in folgenden Fällen einen Fehler zurück.

  • Die @tsql-Eingabe ist kein gültiger Transact-SQL-Batch. Die Gültigkeit wird durch Parsen und 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 demselben Namen wie ein in @params deklarierter Parameter.

  • Die Anweisung verweist auf temporäre Tabellen.

  • Die Abfrage umfasst die Erstellung einer dauerhaften Tabelle, die dann abgefragt wird.

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

Hinweis

Sie müssen die Variable als skalare Transact-SQL-Variable deklarieren oder ein Fehler angezeigt werden.

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.

Sehen Sie sich dies beispielsweise für die Abfrage SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2 an. 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.

Sehen Sie sich dies beispielsweise für die Abfrage SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1) an. 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. In diesem Fall 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 char(30) als Datentyp für @p 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:

    • Ganzzahlige Datentypen (bit, tinyint, smallint, int, bigint)

    • Gelddatentypen (smallmoney, money)

    • Gleitkommadatentypen (float, real)

    • numeric(38, 19). Andere numerische oder dezimale Datentypen werden nicht berücksichtigt.

    • varchar(8000), varchar(max), nvarchar(4000) und nvarchar(max). Andere Zeichenfolgendatentypen (z. B. text, char(8000) oder nvarchar(30)) werden nicht berücksichtigt.

    • varbinary(8000) und varbinary(max). Andere binäre Datentypen (z. B. image, binary(8000) oder varbinary(30)) werden nicht berücksichtigt.

    • date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7). Andere Datums- und Uhrzeittypen, z. B. time(4), werden nicht berücksichtigt.

    • 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) gleich Col_Int + @p, und TT(@p) ist 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 genau eine Konvertierung. Jeder andere Datentyp erzeugt mehr als eine Konvertierung. Da int vor smallint Vorrang hat, wird int für @p 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 tritt bei der Datentypableitung beispielsweise ein Fehler auf, weil jeder Datentyp für @p gleich gut geeignet wäre. Beispielsweise erfolgt keine implizite Konvertierung von int in xml.

  3. Wenn zwei ähnliche Datentypen unter Regel 1 gleichwertig sind, z. B. varchar(8000) und varchar(max), wird der kleinere Datentyp (varchar(8000)) ausgewählt. Dasselbe 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 fester Länge und variabler Länge desselben Datentyps (z. B. char in 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) am besten geeignet 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.

Als letztes Beispiel wird für eine Abfrage SELECT NULL + @p der Datentyp int für @p ausgewählt, weil dies zu einer Konvertierung vom Typ (c) führt.

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'  
  

Weitere Informationen