SQL Server tronque silencieusement les varchar's dans les procédures stockées

Question

Selon cette discussion de forum En effet, le serveur SQL (j'utilise 2005 mais je suppose que cela s'applique également à 2000 et 2008) tronque silencieusement les données de l'utilisateur. varchar que vous spécifiez en tant que paramètres de procédure stockée à la longueur de la variable, même si l'insertion directe de cette chaîne à l'aide d'un fichier de type INSERT provoquerait en fait une erreur. Par exemple, si je crée cette table :

CREATE TABLE testTable(
    [testStringField] [nvarchar](5) NOT NULL
)

alors quand j'exécute ce qui suit :

INSERT INTO testTable(testStringField) VALUES(N'string which is too long')

Je reçois une erreur :

String or binary data would be truncated.
The statement has been terminated.

Super. L'intégrité des données est préservée, et l'appelant le sait. Maintenant, définissons une procédure stockée pour insérer ça :

CREATE PROCEDURE spTestTableInsert
    @testStringField [nvarchar](5)
AS
    INSERT INTO testTable(testStringField) VALUES(@testStringField)
GO

et l'exécuter :

EXEC spTestTableInsert @testStringField = N'string which is too long'

Aucune erreur, 1 ligne affectée. Une ligne est insérée dans la table, avec testStringField comme "strin". Le serveur SQL a tronqué silencieusement les données de la procédure stockée. varchar paramètre.

Ce comportement peut parfois s'avérer pratique, mais je suppose qu'il n'existe AUCUN moyen de le désactiver. C'est extrêmement ennuyeux, car je veulent le truc qui provoque une erreur si je passe une chaîne trop longue à la procédure stockée. Il semble qu'il y ait deux façons de traiter ce problème.

Tout d'abord, déclarez l'objet de la proc stockée @testStringField comme une taille 6, et vérifiez si sa longueur est supérieure à 5. Cela semble être un peu compliqué et implique une quantité irritante de code passe-partout.

Deuxièmement, déclarez simplement que TOUS les paramètres varchar de la procédure stockée doivent être varchar(max) et ensuite laisser le INSERT dans la procédure stockée échoue.

Ce dernier semble fonctionner correctement. Ma question est donc la suivante : est-ce une bonne idée d'utiliser varchar(max) TOUJOURS pour les chaînes de caractères dans les procédures stockées du serveur SQL, si je veux que la procédure stockée échoue lorsqu'une chaîne de caractères trop longue est passée ? Serait-ce même une bonne pratique ? La troncature silencieuse qui ne peut pas être désactivée me semble stupide.

Answer 1

C'est juste est .

Je n'ai jamais remarqué de problème, car l'une de mes vérifications consiste à m'assurer que mes paramètres correspondent à la longueur des colonnes de ma table. Dans le code client également. Personnellement, je m'attendrais à ce que SQL ne voie jamais de données trop longues. Si je voyais des données tronquées, la cause en serait évidente.

Si vous ressentez le besoin d'utiliser varchar(max), faites attention à un problème massif de performance à cause de précédence du type de données . varchar(max) a une priorité plus élevée que varchar(n) (le plus long est le plus élevé). Ainsi, dans ce type de requête, vous obtiendrez un scan et non une recherche et chaque valeur varchar(100) sera CAST en varchar(max).

UPDATE ...WHERE varchar100column = @varcharmaxvalue

Edit :

Il existe un ouvrir l'élément Microsoft Connect concernant cette question.

Et c'est probablement digne d'être inclus dans Les paramètres stricts d'Erland Sommarkog (et correspondant à l'élément Connect ).

Edit 2, après le commentaire de Martins :

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX);
SELECT @sql = 'B', @nsql = 'B'; 
SELECT 
   LEN(@sql), 
   LEN(@nsql), 
   DATALENGTH(@sql), 
   DATALENGTH(@nsql)
;

DECLARE @t table(c varchar(8000));
INSERT INTO @t values (replicate('A', 7500));

SELECT LEN(c) from @t;
SELECT 
   LEN(@sql + c), 
   LEN(@nsql + c), 
   DATALENGTH(@sql + c), 
   DATALENGTH(@nsql + c) 
FROM @t;

Answer 2

Merci, comme toujours, à StackOverflow pour avoir suscité ce genre de discussion approfondie. J'ai récemment passé en revue mes procédures stockées pour les rendre plus robustes en utilisant une approche standard des transactions et des blocs try/catch. Je ne suis pas d'accord avec Joe Stefanelli qui dit : "Ma suggestion serait de rendre l'application responsable", et je suis entièrement d'accord avec Jez : "Il serait préférable que SQL Server vérifie la longueur de la chaîne". Pour moi, l'intérêt d'utiliser des procédures stockées est qu'elles sont écrites dans un langage propre à la base de données et qu'elles doivent servir de dernière ligne de défense. Du côté de l'application, la différence entre 255 et 256 n'est qu'un chiffre sans importance, mais dans l'environnement de la base de données, un champ dont la taille maximale est de 255 n'acceptera tout simplement pas 256 caractères. Les mécanismes de validation de l'application doivent refléter la base de données dorsale du mieux qu'ils peuvent, mais la maintenance est difficile et je veux que la base de données me donne un bon retour d'information si l'application autorise par erreur des données inadéquates. C'est pourquoi j'utilise une base de données au lieu d'un tas de fichiers texte avec CSV ou JSON ou autre.

Je me suis demandé pourquoi l'un de mes SP a produit l'erreur 8152 et un autre a tronqué en silence. J'ai finalement compris : Le SP qui a généré l'erreur 8152 avait un paramètre qui autorisait un caractère de plus que la colonne de table correspondante. La colonne de la table était définie comme nvarchar(255) mais le paramètre était nvarchar(256). Donc, mon "erreur" ne répondrait-elle pas à la préoccupation de gbn : "problème de performance massif" ? Au lieu d'utiliser max, nous pourrions peut-être définir systématiquement la taille de la colonne de la table à, disons, 255 et le paramètre SP à un seul caractère de plus, disons 256. Cela résout le problème de la troncature silencieuse et n'entraîne aucune pénalité de performance. Il existe probablement d'autres inconvénients auxquels je n'ai pas pensé, mais cela me semble être un bon compromis.

Mise à jour : J'ai bien peur que cette technique ne soit pas cohérente. Des tests supplémentaires révèlent que je peux parfois déclencher l'erreur 8152 et parfois les données sont silencieusement tronquées. Je serais très reconnaissant si quelqu'un pouvait m'aider à trouver un moyen plus fiable de traiter ce problème.

Mise à jour 2 : Veuillez voir la réponse de Pyitoechito sur cette page.

Answer 3

Le même comportement peut être observé ici :

declare @testStringField [nvarchar](5)
set @testStringField = N'string which is too long'
select @testStringField

Je suggère de rendre l'application responsable de la validation de l'entrée avant d'appeler la procédure stockée.

Answer 4

Mise à jour : j'ai bien peur que cette technique ne soit pas cohérente. Des tests supplémentaires révèlent que je peux parfois déclencher l'erreur 8152 et que parfois les données sont silencieusement tronquées. Je serais très reconnaissant si quelqu'un pouvait m'aider à trouver un moyen plus fiable de traiter ce problème.

Cela se produit probablement parce que le 256ème caractère de la chaîne est un espace blanc. VARCHAR tronqueront les espaces blancs de fin de ligne lors de l'insertion et généreront simplement un avertissement. Ainsi, votre procédure stockée tronque silencieusement vos chaînes de caractères à 256 caractères, et votre insertion tronque l'espace blanc de fin de chaîne (avec un avertissement). Elle produira une erreur si ce caractère n'est pas un espace blanc.

Peut-être qu'une solution serait de faire en sorte que la procédure stockée VARCHAR une longueur appropriée pour attraper un caractère qui n'est pas un espace blanc. VARCHAR(512) serait probablement assez sûr.

Answer 5

Une solution serait de :

1. Changez tous les paramètres entrants pour qu'ils soient varchar(max)
2. Avoir une variable privée sp de la longueur de données correcte (il suffit de copier et coller tous les paramètres et d'ajouter "int" à la fin).
3. Déclarer une variable de table avec les noms de colonnes identiques aux noms de variables.
4. Insérez dans le tableau une ligne où chaque variable va dans la colonne portant le même nom.
5. Sélectionner à partir du tableau dans les variables internes

De cette façon, vos modifications du code existant seront très minimes, comme dans l'exemple ci-dessous.

C'est le code original :

create procedure spTest
(
    @p1 varchar(2),
    @p2 varchar(3)
)

Voici le nouveau code :

create procedure spTest
(
    @p1 varchar(max),
    @p2 varchar(max)
)
declare @p1Int varchar(2), @p2Int varchar(3)
declare @test table (p1 varchar(2), p2 varchar(3)
insert into @test (p1,p2) varlues (@p1, @p2)
select @p1Int=p1, @p2Int=p2 from @test

Notez que si la longueur des paramètres entrants est supérieure à la limite, au lieu de couper silencieusement la chaîne, le serveur SQL émettra une erreur.