Partager via

sp_describe_undeclared_parameters (Transact-SQL)

  • Article

Retourne un jeu de résultats qui contient des métadonnées sur les paramètres non déclarés dans un lot Transact-SQL. Considère chaque paramètre utilisé dans le lot @tsql, mais non déclaré dans @params. Un jeu de résultats contenant une ligne pour chaque paramètre de ce genre est retourné, avec les informations de type déduites pour ce paramètre. La procédure retourne un jeu de résultats vide si le traitement d'entrée @tsql ne comporte pas de paramètres, sauf ceux déclarés dans @params.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

S'applique à : SQL Server (SQL Server 2012 via la version actuelle, Base de données SQL Windows Azure (version initiale via la version actuelle.

Syntaxe

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

Arguments

  • [ @tsql = ] 'Transact-SQL_batch'
    Une ou plusieurs instructions Transact-SQL Transact-SQL_batch peut être nvarchar(n) ou nvarchar(max).

  • [ @params = ] N'parameters'
    @params fournit une chaîne de déclaration pour les paramètres du lot Transact-SQL, de la même façon que sp_executesql. Parameters peut être nvarchar(n) ou nvarchar(max).

    Chaîne contenant les définitions de tous les paramètres qui ont été incorporés dans Transact-SQL_batch. Cette chaîne doit être une constante Unicode ou une variable Unicode. Chaque définition de paramètre se compose d'un nom de paramètre et d'un type de données. n correspond à un espace réservé pour d'autres définitions de paramètres. Si l'instruction Transact-SQL ou le lot dans l'instruction ne contient aucun paramètre, il est inutile d'utiliser @params. La valeur par défaut de ce paramètre est NULL.

  • Type de données
    Type de données du paramètre.

Valeurs des codes de retour

sp_describe_undeclared_parameters retourne toujours le statut de retour de zéro en cas de réussite. Si la procédure génère une erreur et que la procédure est appelée comme RPC, le statut de retour est déterminé par le type d'erreur comme décrit dans la colonne error_type de sys.dm_exec_describe_first_result_set. Si la procédure est appelée depuis Transact-SQL, la valeur de retour est toujours égale à zéro, même en cas d'erreur.

Jeux de résultats

sp_describe_undeclared_parameters retourne le jeu de résultats suivant.

Nom de la colonne

Type de données

Description

parameter_ordinal

int NOT NULL

Contient la position ordinale du paramètre dans le jeu de résultats. La position du premier paramètre sera spécifiée comme 1.

name

sysname NOT NULL

Contient le nom du paramètre.

suggested_system_type_id

int NOT NULL

Contient le system_type_id du type de données du paramètre comme spécifié dans sys.types.

Pour les types CLR, bien que la colonne system_type_name retourne NULL, cette colonne retournera la valeur 240.

suggested_system_type_name

nvarchar (256) NULL

Contient le nom du type de données. Inclut des arguments (tels que la longueur, la précision, l'échelle) spécifiés pour le type de données du paramètre. Si le type de données est un type d'alias défini par l'utilisateur, le type de système sous-jacent est spécifié ici. S'il s'agit d'un type de données CLR défini par l'utilisateur, NULL est retourné dans cette colonne. Si le type du paramètre ne peut pas être déduit, NULL est retourné.

suggested_max_length

smallint NOT NULL

Consultez sys.columns. pour la description de la colonne max_length.

suggested_precision

tinyint NOT NULL

Consultez sys.columns. pour la description de la colonne de précision.

suggested_scale

tinyint NOT NULL

Consultez sys.columns. pour la description de la colonne d'échelle.

suggested_user_type_id

int NULL

Pour les types d'alias et CLR, contient l'information user_type_id du type de données de la colonne comme spécifié dans sys.types. Sinon, la valeur est NULL.

suggested_user_type_database

sysname NULL

Pour les types d'alias et CLR, contient le nom de la base de données dans laquelle le type est défini. Sinon, la valeur est NULL.

suggested_user_type_schema

sysname NULL

Pour les types d'alias et CLR, contient le nom du schéma dans lequel le type est défini. Sinon, la valeur est NULL.

suggested_user_type_name

sysname NULL

Pour les types d'alias et CLR, contient le nom du type. Sinon, la valeur est NULL.

suggested_assembly_qualified_type_name

nvarchar (4000) NULL

Pour les types CLR, retourne le nom de l'assembly et de la classe qui définit le type. Sinon, la valeur est NULL.

suggested_xml_collection_id

int NULL

Contient l'information xml_collection_id du type de données du paramètre comme spécifié dans sys.columns. Cette colonne retournera NULL si le type retourné n'est pas associé à une collection de schémas XML.

suggested_xml_collection_database

sysname NULL

Contient la base de données dans laquelle la collection de schémas XML associée à ce type est définie. Cette colonne retournera NULL si le type retourné n'est pas associé à une collection de schémas XML.

suggested_xml_collection_schema

sysname NULL

Contient le schéma dans lequel la collection de schémas XML associée à ce type est définie. Cette colonne retournera NULL si le type retourné n'est pas associé à une collection de schémas XML.

suggested_xml_collection_name

sysname NULL

Contient le nom de la collection de schémas XML associé à ce type. Cette colonne retournera NULL si le type retourné n'est pas associé à une collection de schémas XML.

suggested_is_xml_document

bit NOT NULL

Retourne 1 si le type qui est retourné est XML et que ce type est garanti être un document XML. Dans le cas contraire, retourne la valeur 0.

suggested_is_case_sensitive

bit NOT NULL

Retourne 1 si la colonne est d'un type chaîne sensible à la casse et 0 si ce n'est pas le cas.

suggested_is_fixed_length_clr_type

bit NOT NULL

Retourne 1 si la colonne est d'un type CLR de longueur fixe et 0 si ce n'est pas le cas.

suggested_is_input

bit NOT NULL

Retourne 1 si le paramètre est utilisé n'importe où autre que le côté gauche d'une attribution. Dans le cas contraire, retourne la valeur 0.

suggested_is_output

bit NOT NULL

Retourne 1 si le paramètre est utilisé du côté gauche d'une attribution ou est passé à un paramètre de sortie d'une procédure stockée. Dans le cas contraire, retourne la valeur 0.

formal_parameter_name

sysname NULL

Si le paramètre est un argument d'une procédure stockée ou une fonction définie par l'utilisateur, retourne le nom du paramètre formel correspondant. Dans le cas contraire, la valeur NULL est retournée.

suggested_tds_type_id

int NOT NULL

À usage interne uniquement.

suggested_tds_length

int NOT NULL

À usage interne uniquement.

Notes

sp_describe_undeclared_parameters retourne toujours le statut de retour de zéro.

Le cas d'utilisation le plus courant est celui d'une application qui reçoit une instruction Transact-SQL pouvant contenir des paramètres et devant les traiter d'une certaine façon. Il peut s'agir, par exemple, d'une interface utilisateur (telle qu'ODBCTest ou RowsetViewer) où l'utilisateur fournit une requête avec une syntaxe de paramètre ODBC. L'application doit découvrir dynamiquement le nombre de paramètres et inviter l'utilisateur à fournir chacun d'eux.

Autre exemple : une application doit parcourir les paramètres et obtenir des données pour ces derniers à partir d'un autre emplacement (tel qu'une table), sans entrée utilisateur. Dans ce cas, l'application n'a pas à transmettre toutes les informations de paramètre immédiatement. À la place, l'application peut obtenir toutes les informations de paramètres du fournisseur et obtenir les données proprement dites de la table. Le code écrit à l'aide de sp_describe_undeclared_parameters est plus générique et est moins sujet à modification si la structure de données change ultérieurement.

sp_describe_undeclared_parameters retourne une erreur dans chacun des cas suivants.

  • Si l'entrée @tsql n'est pas un lot Transact-SQL valide. La validité est déterminée en analysant le lot Transact-SQL. Toutes les erreurs provoquées par le lot pendant l'optimisation de requête ou en cours d'exécution ne sont pas prises en compte au moment de déterminer si le lot Transact-SQL est valide.

  • Si @params n'est pas NULL et contient une chaîne qui n'est pas une chaîne de déclaration valide du point de vue syntaxique pour les paramètres, ou s'il contient une chaîne qui déclare un paramètre plusieurs fois.

  • Si le lot Transact-SQL d'entrée déclare une variable locale du même nom qu'un paramètre déclaré dans @params.

  • Si l'instruction crée des tables temporaires.

Si @tsql n'a pas de paramètres, autres que ceux déclarés dans @params, la procédure retourne un jeu de résultats vide.

Algorithme de sélection du paramètre

Dans le cas d'une requête avec des paramètres non déclarés, la déduction de type de données pour les paramètres non déclarés s'effectue en trois étapes.

Étape 1

La première étape dans la déduction du type de données pour une requête avec des paramètres non déclarés consiste à rechercher les types de données de toutes les sous-expressions dont les types de données ne dépendent pas des paramètres non déclarés. Le type peut être déterminé pour les expressions suivantes :

  • Colonnes, constantes, variables et paramètres déclarés.

  • Résultats d'un appel à une fonction définie par l'utilisateur (UDF).

  • Expression dont les types de données ne dépendent pas des paramètres non déclarés pour toutes les entrées.

Par exemple, envisagez la requête SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. Les expressions dbo.tbl(@p1) + c1 et c2 ont des types de données, ce qui n'est pas le cas de l'expression @p1 et @p2 + 2.

Après cette étape, si une expression (autre qu'un appel à une fonction UDF) compte deux arguments sans types de données, la déduction du type se solde par une erreur. Les exemples suivants entraînent tous des erreurs :

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

L'exemple suivant ne génère pas d'erreur :

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

Étape 2

Pour un paramètre non déclaré donné @p, l'algorithme de déduction du type recherche l'expression E(@p) la plus imbriquée qui contient @p et présente l'un des types suivants :

  • Argument d'une comparaison ou d'opérateur d'assignation.

  • Argument d'une fonction définie par l'utilisateur (notamment une fonction UDF table), d'une procédure ou d'une méthode.

  • Argument d'une clause VALUES d'une instruction INSERT.

  • Argument d'un CAST ou CONVERT.

L'algorithme de déduction du type recherche un type de données cible TT(@p) pour E(@p). Les types de données cibles des exemples précédents sont les suivants :

  • Type de données de l'autre côté de la comparaison ou de l'attribution.

  • Type de données déclaré du paramètre auquel cet argument est passé.

  • Type de données de la colonne dans laquelle cette valeur est insérée.

  • Type de données vers lequel l'instruction effectue la conversion.

Par exemple, envisagez la requête SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). E(@p1) = @p1, E(@p2) = @p2 + c1, TT(@p1) est le type de données de retour déclaré de dbo.tbl, et TT(@p2) est le type de données du paramètre déclaré pour dbo.tbl.

Si @p n'est pas contenu dans une expression répertoriée au début de l'étape 2, l'algorithme de déduction du type détermine qu'E(@p) est la plus grande expression scalaire qui contient @p, et l'algorithme de déduction du type ne calcule pas de type de données cible TT(@p) pour E(@p). Par exemple, si la requête est SELECT @p + 2, E(@p) = @p + 2, il n'existe aucun TT(@p).

Étape 3

Maintenant que E(@p) et TT(@p) sont identifiés, l'algorithme de déduction du type déduit un type de données pour @p de l'une des deux manières suivantes :

  • Déduction simple

    Si E(@p) = @p et TT(@p) existe, c.-à-d., si @p est directement un argument à l'une des expressions répertoriées au début de l'étape 2, l'algorithme de déduction du type déduit le type de données de @p comme étant TT(@p). Exemple :

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

    Le type de données pour @p1, @p2 et @p3 correspondra respectivement au type de données de c1, au type de données de retour de dbo.tbl et au type de données de paramètre pour dbo.tbl.

    Exemple de cas particulier : si @p est un argument d'un opérateur <, >, <= ou >=, les règles de déduction simples ne s'appliquent pas. L'algorithme de la déduction du type utilisera les règles de déduction générales expliquées dans la section suivante. Par exemple, si c1 est une colonne de type de données char(30), considérez les deux requêtes suivantes :

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

    Dans le premier cas, l'algorithme de déduction du type déduit char(30) comme type de données pour @p conformément aux règles précédemment énoncées dans cette rubrique. Dans le deuxième cas, l'algorithme de déduction du type déduit varchar(8000) d'après les règles de déduction générales décrites dans la section suivante.

  • Déduction générale

    Si la déduction simple ne s'applique pas, les types de données suivants sont considérés pour les paramètres non déclarés :

    • Types de données d'entier (bit, tinyint, smallint, int, bigint)

    • Types de données monétaires (smallmoney, money)

    • Types de données à virgule flottante (float, real)

    • numeric(38, 19) - Les autres types de données numériques ou décimales ne sont pas considérés.

    • varchar(8000), varchar(max), nvarchar(4000) et nvarchar(max)- Les autres types de données string (tels que text, char(8000), nvarchar(30), etc.) ne sont pas considérés.

    • varbinary(8000) et varbinary(max) - Les autres types de données binaires ne sont pas considérés (tels que image, binary(8000), varbinary(30), etc.).

    • date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) - Les autres types de date et d'heure, tels que time(4), ne sont pas considérés.

    • sql_variant

    • xml

    • Types CLR définis par le système (hierarchyidgeometry, geography)

    • Types CLR définis par l'utilisateur

Critères de sélection

Parmi les types de données candidats, tout type de données qui invaliderait la requête est rejeté. Parmi les types de données candidats restants, l'algorithme de déduction du type en sélectionne un d'après les règles suivantes.

  1. Le type de données qui produit le plus petit nombre de conversions implicites dans E(@p) est sélectionné. Si un type de données particulier produit un type de données pour E(@p) qui est différent de TT(@p), l'algorithme de déduction du type considère qu'il s'agit d'une conversion implicite supplémentaire du type de données E(@p) en TT(@p).

    Exemple :

    SELECT * FROM t WHERE Col_Int = Col_Int + @p

    Dans ce cas, E(@p) est Col_Int + @p et TT(@p) est de type int. int est choisi pour @p car il ne produit pas de conversions implicites. Tout autre choix de type de données produit au moins une conversion implicite.

  2. Si plusieurs types de données sont liés pour le plus petit nombre de conversions, le type de données dont la priorité est supérieure est utilisé. Par exemple

    SELECT * FROM t WHERE Col_Int = Col_smallint + @p

    Dans ce cas, int et smallint entraînent une conversion. Chaque autre type de données entraîne plusieurs conversions. Étant donné que int a priorité sur smallint, int est utilisé pour @p. Pour plus d'informations sur la priorité des types de données, consultez Priorités des types de données (Transact-SQL).

    Cette règle s'applique uniquement s'il existe une conversion implicite entre chaque type de données lié d'après la règle 1 et le type de données présentant la priorité la plus élevée. S'il n'existe aucune conversion implicite, la déduction du type de données se solde par une erreur. Par exemple dans la requête SELECT @p FROM t, la déduction du type de données échoue car chaque type de données pour @p est équivalent du point de vue de son adéquation. Par exemple, il n'existe aucune conversion implicite du type int en xml.

  3. Si deux types de données semblables sont liés sous la règle 1, par exemple varchar(8000) et varchar(max), c'est le plus petit type de données (varchar(8000)) qui est choisi. Le même principe s'applique aux types de données nvarchar et varbinary.

  4. Pour les besoins de la règle 1, l'algorithme de la déduction du type préfère certaines conversions plutôt que d'autres. Conversions classées de la meilleure à la pire :

    1. Conversion entre un même type de données de base de longueur différente.

    2. Conversion entre une version de longueur fixe et une version de longueur variable de mêmes types de données (par exemple, char en varchar).

    3. Conversion entre NULL et int.

    4. Toute autre conversion.

Par exemple, pour la requête SELECT * FROM t WHERE [Col_varchar(30)] > @p, varchar(8000) est choisi parce que la conversion (a) est préférable. Pour la requête SELECT * FROM t WHERE [Col_char(30)] > @p, varchar(8000) est encore choisi car il entraîne une conversion de type (b), et qu'un autre choix (tel que varchar(4000)) entraînerait une conversion de type (d).

Comme dernier exemple, considérons une requête SELECT NULL + @p, int est choisi pour @p car il entraîne une conversion de type (c).

Autorisations

Requiert l'autorisation d'exécuter l'argument @tsql.

Exemples

L'exemple suivant retourne des informations telles que le type de données attendu pour les paramètres non déclarés @id et @name.

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

Lorsque le paramètre @id est fourni comme référence @params, le paramètre @id est omis du jeu de résultats et seul le paramètre @name est décrit.

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'

Voir aussi

Référence

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)