sp_describe_undeclared_parameters (Transact-SQL)

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics Point de terminaison analytique SQL dans Microsoft Fabric Entrepô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'@parameter_name data_type [ , ... n ]' ]

Arguments

[ @tsql = ] 'tsql'

Une ou plusieurs instructions Transact-SQL. @tsql peut être nvarchar(n) ou nvarchar(max).

[ @params = ] N'@parameter_name data_type [ ,... n ]'

@params fournit une chaîne de déclaration pour les paramètres du lot Transact-SQL, de la même façon que le fonctionnementsp_executesql. @params peut être nvarchar(n) ou nvarchar(max).

Chaîne qui contient les définitions de tous les paramètres incorporés dans @tsql. 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 de l’instruction ne contient pas de paramètres, @params n’est pas obligatoire. La valeur par défaut de ce paramètre est NULL.

Valeurs des codes de retour

sp_describe_undeclared_parameters retourne toujours l’état zéro sur la réussite. Si la procédure lève une erreur et que la procédure est appelée en tant que RPC, l’état de retour est rempli par le type d’erreur, comme décrit dans la error_type colonne 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.

Jeu de résultats

sp_describe_undeclared_parameters retourne le jeu de résultats suivant.

Notes

sp_describe_undeclared_parameters retourne toujours l’état 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. Par exemple, il s’agit d’une interface utilisateur (telle que ODBCTest ou RowsetViewer) où l’utilisateur fournit une requête avec la syntaxe des paramètres 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 n’a pas besoin de transmettre toutes les 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. L’utilisation sp_describe_undeclared_parameters du code est plus générique et est moins susceptible de nécessiter une modification si la structure de données change ultérieurement.

sp_describe_undeclared_parameters retourne une erreur dans l’un des cas suivants.

• L’entrée @tsql n’est pas un lot Transact-SQL valide. La validité est déterminée en analysant le lot Transact-SQL. Les erreurs provoquées par le lot lors de l’optimisation de la requête ou pendant l’exécution ne sont pas prises en compte lors de la détermination de la validité du lot Transact-SQL.

• @params n’est pas NULL et contient une chaîne qui n’est pas une chaîne de déclaration valide de manière syntactique pour les paramètres, ou si elle contient une chaîne qui déclare un paramètre plusieurs fois.

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

• L’instruction fait 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 aucun paramètre, autre que les paramètres déclarés dans @params, la procédure retourne un jeu de résultats vide.

Algorithme de sélection de paramètres

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 : Rechercher les types de données des sous-expressions

La première étape de la déduction de 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 avec des types de données qui 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 et une expression @p1 et @p2 + 2 non.

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 : Rechercher les expressions les plus internes

Pour un paramètre @pnon déclaré donné, l’algorithme de déduction de type recherche l’expression E(@p) la plus interne qui contient @p et est l’un des arguments 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 VALUES clause d’une INSERT instruction.
• Argument à un CAST ou CONVERT.

L’algorithme de déduction de type recherche un type TT(@p) de données cible 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). EnsuiteE(@p1) = @p1, est TT(@p1) E(@p2) = @p2 + c1le type de données de retour déclaré de , et TT(@p2) est le type de dbo.tbldonnées de paramètre déclaré pour dbo.tbl.

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

Étape 3 : Déduire les types de données

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

• Déduction simple

  S’il E(@p) = @p existe et TT(@p) s’il existe, c’est-à-dire s’il @p s’agit directement d’un argument à l’une des expressions répertoriées au début de l’étape 2, l’algorithme de déduction de type déduit le type de données à @p être TT(@p). Par exemple :

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

  Le type de données pour @p1, @p2et @p3 sera le type de données de , le type de c1données de retour de , et le type de dbo.tbldonnées de paramètre pour dbo.tbl respectivement.

  En tant que cas spécial, s’il s’agit @p d’un argument à un <, ou ><=>= opérateur, les règles de déduction simple 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, s’il s’agit c1 d’une colonne de type de données char(30), tenez compte des 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 de type déduit char(30) comme type de données pour @p les règles décrites plus haut dans cet article. 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 pris en compte 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écimaux ne sont pas pris en compte.

  • 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 pris en compte.

  • varbinary(8000) et varbinary(max) : les autres types de données binaires ne sont pas pris en compte (par exemple, image, binary(8000), varbinary(30), etc.).

  • date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) - Autres types de date et d’heure, tels que 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 est E(@p) sélectionné. Si un type de données particulier produit un type de données différent E(@p) de celui-ci, l’algorithme de TT(@p)déduction de type considère qu’il s’agit d’une conversion implicite supplémentaire du type de données vers E(@p) 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 int. int est choisi car @p il ne produit aucune conversion implicite. 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. Comme int est prioritaire 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.

   Cette règle s’applique uniquement s’il existe une conversion implicite entre chaque type de données qui lie selon la règle 1 et le type de données avec la plus grande priorité. En l’absence de conversion implicite, la déduction du type de données échoue avec une erreur. Par exemple, dans la requête SELECT @p FROM t, la déduction de type de données échoue, car tout type @p de données est tout aussi bon. Par exemple, il n’existe aucune conversion implicite d’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 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).

En guise d’exemple final, étant donné une requêteSELECT NULL + @p, int est choisi car @p elle génère une conversion de type (c).

autorisations

Nécessite 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.

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

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

Contenu connexe

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