Teilen über

sp_describe_undeclared_parameters (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL verwaltete Instanz Azure Synapse Analytics SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric Warehouse

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'params [ , ...n ]' ]

Hinweis

Um diese gespeicherte Prozedur in Azure Synapse Analytics in einem dedizierten SQL-Pool zu verwenden, legen Sie die Datenbankkompatibilitätsstufe auf 20 oder höher fest. Zum Deaktivieren ändern Sie die Datenbankkompatibilitätsstufe in 10.

Argumente

[ @tsql = ] 'tsql'

Mindestens eine Transact-SQL-Anweisung. @tsql kann nvarchar(n) oder nvarchar(max)sein.

[ @params = ] N'params'

@params stellt eine Deklarationszeichenfolge für Parameter für den Transact-SQL-Batch bereit, ähnlich wie bei sp_executesql der Funktionsweise. @params kann nvarchar(n) oder nvarchar(max)sein.

Eine Zeichenfolge, die die Definitionen aller Parameter enthält, die in @tsql eingebettet sind. Die Zeichenfolge muss eine Unicode-Konstante oder eine Unicode-Variable sein. Jede Parameterdefinition besteht aus einem Parameternamen und einem Datentyp. n ist 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.

Rückgabecodewerte

sp_describe_undeclared_parameters gibt immer den Status null bei Erfolg zurück. Wenn die Prozedur einen Fehler auslöst und die Prozedur als RPC aufgerufen wird, wird der Rückgabestatus durch den Fehlertyp aufgefüllt, wie in der error_type Spalte von sys.dm_exec_describe_first_result_set. Wenn die Prozedur über Transact-SQL aufgerufen wird, lautet der Rückgabewert immer 0, auch bei Fehlern.

Resultset

sp_describe_undeclared_parameters gibt das folgende Resultset zurück.

Spaltenname Datentyp BESCHREIBUNG
parameter_ordinal int Enthält die Ordnungsposition des Parameters im Resultset. Die Position des ersten Parameters wird als 1angegeben. Lässt keine NULL-Werte zu.
name sysname Enthält den Namen des Parameters. Lässt keine NULL-Werte zu.
suggested_system_type_id int Enthält den system_type_id Datentyp des Parameters gemäß der Angabe in sys.types.

Bei CLR-Typen gibt diese Spalte den Wert 240zurück, obwohl die system_type_name Spalte zurückgegeben NULLwird. Lässt keine NULL-Werte zu.
suggested_system_type_name nvarchar(256) 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, NULL wird in dieser Spalte zurückgegeben. Wenn der Typ des Parameters nicht abgeleitet werden kann, NULL wird zurückgegeben. NULL-Werte sind zulässig.
suggested_max_length smallint Siehe sys.columns. für max_length spaltenbeschreibung. Lässt keine NULL-Werte zu.
suggested_precision tinyint Siehe sys.columns. enthält eine Beschreibung der Genauigkeitsspalte. Lässt keine NULL-Werte zu.
suggested_scale tinyint Siehe sys.columns. enthält eine Beschreibung der Skalierungsspalte. Lässt keine NULL-Werte zu.
suggested_user_type_id int Enthält für CLR- und Aliastypen den user_type_id Datentyp der Spalte, wie in sys.typesangegeben. Andernfalls lautet NULL. NULL-Werte sind zulässig.
suggested_user_type_database sysname Enthält bei CLR- und Aliastypen den Namen der Datenbank, in der der Typ definiert wurde. Andernfalls lautet NULL. NULL-Werte sind zulässig.
suggested_user_type_schema sysname Enthält bei CLR- und Aliastypen den Namen des Schemas, in dem der Typ definiert wurde. Andernfalls lautet NULL. NULL-Werte sind zulässig.
suggested_user_type_name sysname Enthält bei CLR- und Aliastypen den Namen des Typs. Andernfalls lautet NULL.
suggested_assembly_qualified_type_name nvarchar(4000) Gibt bei CLR-Typen den Namen der Assembly und der Klasse zurück, die den Typ definiert. Andernfalls lautet NULL. NULL-Werte sind zulässig.
suggested_xml_collection_id int Enthält den xml_collection_id Datentyp des Parameters gemäß der Angabe in sys.columns. Diese Spalte gibt zurück NULL , wenn der zurückgegebene Typ keiner XML-Schemaauflistung zugeordnet ist. NULL-Werte sind zulässig.
suggested_xml_collection_database sysname Enthält die Datenbank, in der die XML-Schemaauflistung definiert ist, die diesem Typ zugeordnet wurde. Diese Spalte gibt zurück NULL , wenn der zurückgegebene Typ keiner XML-Schemaauflistung zugeordnet ist. NULL-Werte sind zulässig.
suggested_xml_collection_schema sysname Enthält das Schema, in dem die XML-Schemaauflistung definiert ist, die diesem Typ zugeordnet wurde. Diese Spalte gibt zurück NULL , wenn der zurückgegebene Typ keiner XML-Schemaauflistung zugeordnet ist. NULL-Werte sind zulässig.
suggested_xml_collection_name sysname Enthält den Namen der XML-Schemaauflistung, die diesem Typ zugeordnet wurde. Diese Spalte gibt zurück NULL , wenn der zurückgegebene Typ keiner XML-Schemaauflistung zugeordnet ist. NULL-Werte sind zulässig.
suggested_is_xml_document bit Gibt zurück 1 , wenn der zurückgegebene Typ XML ist und dieser Typ garantiert ein XML-Dokument ist. Andernfalls wird 0 zurückgegeben. Lässt keine NULL-Werte zu.
suggested_is_case_sensitive bit Gibt zurück 1 , wenn die Spalte einen Zeichenfolgentyp mit Groß-/Kleinschreibung aufweist und 0 wenn dies nicht der Fall ist. Lässt keine NULL-Werte zu.
suggested_is_fixed_length_clr_type bit Gibt zurück 1 , wenn die Spalte einen CLR-Typ mit fester Länge aufweist und 0 nicht. Lässt keine NULL-Werte zu.
suggested_is_input bit Gibt zurück 1 , wenn der Parameter an einer anderen Stelle als der linken Seite einer Zuordnung verwendet wird. Andernfalls wird 0 zurückgegeben. Lässt keine NULL-Werte zu.
suggested_is_output bit Gibt zurück 1 , wenn der Parameter auf der linken Seite einer Zuordnung verwendet wird oder an einen Ausgabeparameter einer gespeicherten Prozedur übergeben wird. Andernfalls wird 0 zurückgegeben. Lässt keine NULL-Werte zu.
formal_parameter_name sysname 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. NULL-Werte sind zulässig.
suggested_tds_type_id int Nur zur internen Verwendung. Lässt keine NULL-Werte zu.
suggested_tds_length int Nur zur internen Verwendung. Lässt keine NULL-Werte zu.

Hinweise

sp_describe_undeclared_parameters gibt immer den Status null 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. Ein Beispiel ist eine Benutzeroberfläche (z ODBCTest . B. oder RowsetViewer), auf der der Benutzer eine Abfrage mit ODBC-Parametersyntax bereitstellt. 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 gleichzeitig übergeben. Stattdessen kann die Anwendung alle Parameterinformationen vom Anbieter und die Daten selbst aus der Tabelle abrufen. Die Verwendung von sp_describe_undeclared_parameters Code ist allgemeiner und erfordert weniger Änderungen, wenn sich die Datenstruktur später ändert.

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

  • Die Eingabe @tsql ist kein gültiger Transact-SQL-Batch. Die Gültigkeit wird durch Parsen und Analysieren des Transact-SQL-Batchs bestimmt. Alle Fehler, die während der Abfrageoptimierung oder während der Ausführung durch den Batch verursacht werden, werden nicht berücksichtigt, wenn ermittelt wird, ob der Transact-SQL-Batch gültig ist.

  • @params ist nicht NULL und enthält eine Zeichenfolge, die keine syntaktisch gültige Deklarationszeichenfolge für Parameter ist, oder wenn sie eine Zeichenfolge enthält, die einen Parameter mehr als einmal 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 keine Parameter aufweist, außer parametern, die in @params deklariert sind, 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.

Parameterauswahlalgorithmus

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

Schritt 1: Suchen der Datentypen der Unterausdrücke

Der erste Schritt beim Datentypabzug für eine Abfrage mit nicht deklarierten Parametern besteht darin, die Datentypen aller Unterausdrücke zu finden, 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 von den nicht deklarierten Parametern für alle Eingaben 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 und c2 weisen Datentypen dbo.tbl(@p1) + c1 und Ausdrücke @p1 auf und @p2 + 2 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: Suchen innerster Ausdrücke

Bei einem bestimmten nicht deklarierten Parameter @pfindet der Typabzugsalgorithmus den innersten Ausdruck E(@p) , der eines der folgenden Argumente enthält @p und 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 ein CAST oder CONVERT.

Der Typabzugsalgorithmus findet einen Zieldatentyp TT(@p) für E(@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. Anschließend ist , TT(@p1) ist der deklarierte Rückgabedatentyp von dbo.tbl, und TT(@p2) ist der deklarierte Parameter-Datentyp für dbo.tbl.E(@p1) = @p1E(@p2) = @p2 + c1

Wenn @p nicht in einem Ausdruck enthalten ist, der am Anfang von Schritt 2 aufgeführt ist, bestimmt der Typabzugsalgorithmus, dass E(@p) es sich um den größten skalaren Ausdruck handelt, der enthält @p, und der Typabzugsalgorithmus berechnet keinen Zieldatentyp TT(@p) für E(@p). Beispiel: Wenn die Abfrage dann E(@p) = @p + 2ist SELECT @p + 2 , und es gibt keine TT(@p).

Schritt 3: Ableiten von Datentypen

E(@p) Da der Typabzugsalgorithmus nun identifiziert wirdTT(@p), leitet der Typabzugsalgorithmus einen Datentyp @p auf eine der folgenden beiden Arten ab:

  • Einfache Ableitung

    Wenn E(@p) = @p und TT(@p) vorhanden ist, d. h. wenn @p es sich direkt um ein Argument für einen der ausdrücke handelt, die am Anfang von Schritt 2 aufgeführt sind, leitet der Typabzugsalgorithmus den Datentyp der @p zu sein.TT(@p) Zum Beispiel:

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

    Der Datentyp für @p1, @p2und @p3 ist der Datentyp von c1, der Rückgabedatentyp von dbo.tbl, und der Parameter-Datentyp für dbo.tbl die jeweilige.

    Wenn es @p sich um ein Argument für ein <, >, oder <=>= einen Operator handelt – gelten keine einfachen Abzugsregeln. Der Typableitungsalgorithmus verwendet die allgemeinen, im nächsten Abschnitt erklärten Ableitungsregeln. Wenn c1 es sich z. B. um eine Spalte vom Datentyp Char(30) handelt, sollten Sie die folgenden beiden Abfragen berücksichtigen:

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

    Im ersten Fall leitet der Typabzugsalgorithmus Char(30) als Datentyp für @p die weiter oben in diesem Artikel beschriebenen Regeln ab. Im zweiten Fall leitet der Typableitungsalgorithmus anhand der im nächsten Abschnitt erläuterten allgemeinen Ableitungsregeln varchar(8000) ab.

  • Allgemeine Ableitung

    Wenn kein einfacher Abzug gilt, werden die folgenden Datentypen für nicht deklarierte Parameter berücksichtigt:

    • 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 werden nicht berücksichtigt (z . B. Bild, Binary(8000), varbinary(30), etc.).

    • 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, der die kleinste Anzahl impliziter Konvertierungen E(@p) erzeugt, wird ausgewählt. Wenn ein bestimmter Datentyp einen Datentyp erzeugt, der E(@p) sich von TT(@p)dem unterscheidet, betrachtet der Typabzugsalgorithmus dies als zusätzliche implizite Konvertierung vom Datentyp in E(@p) TT(@p).

    Zum Beispiel:

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

    In diesem Fall E(@p) ist Col_Int + @p und TT(@p) ist int. int wird ausgewählt @p , weil sie 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. Zum 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 Vorrang vor smallint hat, wird int für @p. Weitere Informationen zur Rangfolge des Datentyps finden Sie unter "Rangfolge des Datentyps".

    Diese Regel gilt nur, wenn es eine implizite Konvertierung zwischen jedem Datentyp gibt, der gemäß Regel 1 und dem Datentyp mit der größten Rangfolge verknüpft ist. Wenn keine implizite Konvertierung vorhanden ist, schlägt der Datentypabzug mit einem Fehler fehl. Beispielsweise schlägt der Datentypabzug in der Abfrage SELECT @p FROM tfehl, da jeder Datentyp @p gleich gut wäre. Beispielsweise gibt es 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 fester und variabler Version derselben Datentypen (z . B. Zeichen 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 int eine Abfrage SELECT NULL + @pausgewählt@p, da sie zu einer Typkonvertierung (c) führt.

Berechtigungen

Erfordert die Berechtigung zum Ausführen 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.

EXEC 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.

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';

Zugehöriger Inhalt