Prévention des débordements de colonnes Sql

Si j'ai une procédure stockée paramétrée qui prend une valeur varchar (10) et la convertit en int, je dois actuellement m'assurer que la valeur n'est pas supérieure à l'équivalent varchar de la valeur int maximale.

IF @Criteria <= '2147483647' SET @Id = CONVERT(int, @Criteria) 

Ma question: Y at-il un meilleur moyen d'éviter de déborder une colonne int lors de la conversion d'une valeur varchar?

Edit: Oui, évidemment, si je pensais que la valeur allait légitimement contenir quelque chose proche de la valeur maximale, je pourrais étendre à BigInt. Cela est vraiment destiné à gérer les appels inappropriés à ce proc stocké, et était juste une question d'usage général sur l'utilisation de Convert () dans les cas où la valeur résultante pourrait déborder le type de données souhaité.

Pour traiter les différentes conditions (espaces, décimales, etc.), enveloppez le converti dans un TRY / CATCH si vous ne pouvez pas nettoyer sur le client. Suppose SQL Server 2005

 ... BEGIN TRY SET @Id = CONVERT(int, @Criteria) END TRY BEGIN CATCH SET @Id = NULL END CATCH ... 

Votre test ne fonctionnera pas de manière fiable.

Si @Criteria contient '11111111111111111111', il sortinge less que votre nombre magique, car vous faites une comparaison de strings.

Le plus simple et le meilleur moyen est de le traiter à la source, qui est partout où le varchar est créé. Ou bien expliquez ce que vous entendez par "empêcher le débordement". Qu'attendez-vous quand le varchar est trop long?

Et si vous passez dans des espaces? – il passera la condition IF mais échouera à la conversion. Vous devriez aussi utiliser ISNUMERIC ().

 IF @Criteria <= '2147483647' AND ISNUMERIC(@Criteria) SET @Id = CONVERT(int, @Criteria) 

Une meilleure question pourrait être pourquoi vous stockez, comme varchars, des entiers qui déborderaient leur colonne de destination. Je ne suis pas sûr de ce que vous pouvez faire pour éviter complètement les débordements; vous pouvez envisager de basculer Id vers un entier non signé, de sorte que vous pouvez get jusqu'à 2 ^ 32 bits disponibles (je suppose que les critères ne sont jamais négatifs, car vous l'atsortingbuez à une colonne d'identification).

Je ne suis pas sûr si SQL Server le supporte, mais MySQL a des colonnes BIGINT, qui vont jusqu'à 2 ^ 64 (2 ^ 63 si vous voulez le signer).

Premièrement, votre longueur d'input de 10 semble ne pas vous attendre (ou accepter) des valeurs négatives. La borne basse pour int est -2147483648, qui serait représentée par une string de 11 caractères.

En me basant sur le code de DJ ci-dessus, je vous suggère de mettre l'appel ISNUMERIC () avant le CONVERT / compare.

 IF ISNUMERIC(@Criteria) = 1 AND CONVERT(bigint, @Criteria) <= 2147483647 SET @Id = CONVERT(int, @Criteria) 

Cela se convertit en un bigint d'abord, puis compare. Voici quelques cas de test:

 DECLARE @Id int DECLARE @Criteria varchar(10) PRINT 'Expect failure (NULL)' SET @Criteria = '2147483648' SET @Id = NULL IF ISNUMERIC(@Criteria) = 1 AND CONVERT(bigint, @Criteria) <= 2147483647 SET @Id = CONVERT(int, @Criteria) SELECT @Id AS '@Id', @Criteria AS '@Criteria', CONVERT(bigint, @Criteria) AS 'Converted to bigint' PRINT 'Expect success' SET @Criteria = '2147483647' SET @Id = NULL IF ISNUMERIC(@Criteria) = 1 AND CONVERT(bigint, @Criteria) <= 2147483647 SET @Id = CONVERT(int, @Criteria) SELECT @Id AS '@Id', @Criteria AS '@Criteria', CONVERT(bigint, @Criteria) AS 'Converted to bigint' PRINT 'Expect failure but get success because @Criteria is truncated to 10 characters' SET @Criteria = '11111111111111111111' SET @Id = NULL IF ISNUMERIC(@Criteria) = 1 AND CONVERT(bigint, @Criteria) <= 2147483647 SET @Id = CONVERT(int, @Criteria) SELECT @Id AS '@Id', @Criteria AS '@Criteria', CONVERT(bigint, @Criteria) AS 'Converted to bigint' 

et résultats:

 Expect failure (NULL) @Id @Criteria Converted to bigint ----------- ---------- -------------------- NULL 2147483648 2147483648 Expect success @Id @Criteria Converted to bigint ----------- ---------- -------------------- 2147483647 2147483647 2147483647 Expect failure but get success because @Criteria is truncated to 10 characters @Id @Criteria Converted to bigint ----------- ---------- -------------------- 1111111111 1111111111 1111111111 

Notez que passer '11111111111111111111' fonctionne réellement puisque l'input est tronquée.