sp_describe_undeclared_parameters (Transact-SQL)

Article
08/21/2024

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

Remarque

Pour utiliser cette procédure stockée dans Azure Synapse Analytique dans un pool SQL dédié, définissez le niveau 20 de compatibilité de la base de données sur ou supérieur. Pour désactiver, remplacez le niveau 10de compatibilité de la base de données par .

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.

Nom de la colonne	Type de données	Description
`parameter_ordinal`	int	Contient la position ordinale du paramètre dans le jeu de résultats. La position du premier paramètre est spécifiée en tant que `1`. N'accepte pas la valeur NULL.
`name`	sysname	Contient le nom du paramètre. N'accepte pas la valeur NULL.
`suggested_system_type_id`	int	Contient le `system_type_id` type de données du paramètre tel que spécifié dans `sys.types`. Pour les types CLR, même si la `system_type_name` colonne retourne `NULL`, cette colonne renvoie la valeur `240`. N'accepte pas la valeur NULL.
`suggested_system_type_name`	nvarchar (256)	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é. Autorise la valeur Null.
`suggested_max_length`	smallint	Consultez l’article `sys.columns`. pour la `max_length` description de colonne. N'accepte pas la valeur NULL.
`suggested_precision`	tinyint	Consultez l’article `sys.columns`. pour la description de la colonne de précision. N'accepte pas la valeur NULL.
`suggested_scale`	tinyint	Consultez l’article `sys.columns`. pour la description de la colonne d'échelle. N'accepte pas la valeur NULL.
`suggested_user_type_id`	int	Pour les types CLR et alias, contient le `user_type_id` type de données de la colonne tel que spécifié dans `sys.types`. Sinon, c’est `NULL`. Autorise la valeur Null.
`suggested_user_type_database`	sysname	Pour les types d'alias et CLR, contient le nom de la base de données dans laquelle le type est défini. Sinon, c’est `NULL`. Autorise la valeur Null.
`suggested_user_type_schema`	sysname	Pour les types d'alias et CLR, contient le nom du schéma dans lequel le type est défini. Sinon, c’est `NULL`. Autorise la valeur Null.
`suggested_user_type_name`	sysname	Pour les types d'alias et CLR, contient le nom du type. Sinon, c’est `NULL`.
`suggested_assembly_qualified_type_name`	nvarchar(4000)	Pour les types CLR, retourne le nom de l'assembly et de la classe qui définit le type. Sinon, c’est `NULL`. Autorise la valeur Null.
`suggested_xml_collection_id`	int	Contient le `xml_collection_id` type de données du paramètre tel que spécifié dans `sys.columns`. Cette colonne retourne `NULL` si le type retourné n’est pas associé à une collection de schémas XML. Autorise la valeur Null.
`suggested_xml_collection_database`	sysname	Contient la base de données dans laquelle la collection de schémas XML associée à ce type est définie. Cette colonne retourne `NULL` si le type retourné n’est pas associé à une collection de schémas XML. Autorise la valeur Null.
`suggested_xml_collection_schema`	sysname	Contient le schéma dans lequel la collection de schémas XML associée à ce type est définie. Cette colonne retourne `NULL` si le type retourné n’est pas associé à une collection de schémas XML. Autorise la valeur Null.
`suggested_xml_collection_name`	sysname	Contient le nom de la collection de schémas XML associé à ce type. Cette colonne retourne `NULL` si le type retourné n’est pas associé à une collection de schémas XML. Autorise la valeur Null.
`suggested_is_xml_document`	bit	Retourne `1` si le type retourné est XML et que ce type est garanti comme un document XML. Sinon, retourne `0`. N'accepte pas la valeur NULL.
`suggested_is_case_sensitive`	bit	Retourne `1` si la colonne est d’un type de chaîne sensible à la casse et `0` si ce n’est pas le cas. N'accepte pas la valeur NULL.
`suggested_is_fixed_length_clr_type`	bit	Retourne `1` si la colonne est d’un type CLR de longueur fixe et `0` si ce n’est pas le cas. N'accepte pas la valeur NULL.
`suggested_is_input`	bit	Retourne `1` si le paramètre est utilisé n’importe où autre que le côté gauche d’une affectation. Sinon, retourne `0`. N'accepte pas la valeur NULL.
`suggested_is_output`	bit	Retourne `1` si le paramètre est utilisé sur le côté gauche d’une affectation ou est passé à un paramètre de sortie d’une procédure stockée. Sinon, retourne `0`. N'accepte pas la valeur NULL.
`formal_parameter_name`	sysname	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. Sinon, retourne `NULL`. Autorise la valeur Null.
`suggested_tds_type_id`	int	À usage interne uniquement. N'accepte pas la valeur NULL.
`suggested_tds_length`	int	À usage interne uniquement. N'accepte pas la valeur NULL.

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.

Remarque

Vous devez déclarer la variable en tant que variable Transact-SQL scalaire, ou une erreur s’affiche.

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.

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

Partage via

sp_describe_undeclared_parameters (Transact-SQL)

Syntaxe

Arguments

[ @tsql = ] 'tsql'

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

Valeurs des codes de retour

Jeu de résultats

Notes

Algorithme de sélection de paramètres

Étape 1 : Rechercher les types de données des sous-expressions

Étape 2 : Rechercher les expressions les plus internes

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

Critères de sélection

autorisations

Exemples

Commentaires

Ressources supplémentaires

Partage via

sp_describe_undeclared_parameters (Transact-SQL)

Syntaxe

Arguments

[ @tsql = ] 'tsql'

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

Valeurs des codes de retour

Jeu de résultats

Notes

Algorithme de sélection de paramètres

Étape 1 : Rechercher les types de données des sous-expressions

Étape 2 : Rechercher les expressions les plus internes

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

Critères de sélection

autorisations

Exemples

Contenu connexe

Commentaires

Ressources supplémentaires