sp_describe_undeclared_parameters (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsPoint de terminaison analytique SQL dans Microsoft FabricEntrepôt dans Microsoft Fabric

Retourne un jeu de résultats qui contient des métadonnées à propos des 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. Le jeu de résultats retourné contient une ligne pour chaque paramètre de ce genre, 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.

Conventions de la syntaxe Transact-SQL

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 de type 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. Les paramètres peuvent être de type nvarchar(n) ou nvarchar(max).

Chaîne contenant les définitions de tous les paramètres qui ont été incorporés dans le 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.

Datatype
Type de données du paramètre.

Codet 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 rempli par le type d’erreur 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.

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 : en l'absence d'entrée de la part de l'utilisateur, une application doit faire une boucle sur les paramètres et obtenir les données pour ces derniers depuis un autre emplacement (tel qu'une table). Dans ce cas, l'application ne doit pas passer l'ensemble des informations de paramètre à la fois. À 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 référence des tables temporaires.

• La requête inclut la création d'une table permanente qui est alors interrogée.

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.

Prenons l’exemple de 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.

Prenons l’exemple de 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). Par 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 entiers (bit, tinyint, smallint, int, bigint)

  • Types de données money (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) - Autres types de données de chaîne (comme 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 pris en compte (comme image, binary(8000), varbinary(30), etc.).

  • date, time(7),smalldatetime, datetime, datetime2(7), datetimeoffset(7) : les autres types de date et d’heure, comme time(4), ne sont pas pris en compte.

  • sql_variant

  • xml

  • Types définis par le système CLR (hierarchyid, geometry, 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).

   Par 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 parce qu’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 précédence du type de données, consultez Priorité du type 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 similaires sont liés à la règle 1, par exemple varchar(8000) et varchar(max), le type de données le plus petit (varchar(8000)) 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

• 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)