Gestion des erreurs dans SQL Server 2012 – Bien choisir son serveur d impression

Depuis la sortie de SQL Server 2005, vous pouvez gérer les erreurs dans votre code T-SQL en incluant un ESSAYEZ … bloc qui contrôle le flux de votre script en cas d'erreur, semblable à la façon dont les langages procéduraux ont traditionnellement traité les erreurs. le ESSAYEZ … Le bloc facilite le retour ou l'audit des données liées aux erreurs, ainsi que la prise d'autres mesures. Et dans le bloc en particulier, le CAPTURE portion-vous avez pu inclure un RAISERROR afin de renvoyer les données relatives aux erreurs à l'application appelante. Cependant, avec la version de SQL Server 2012, vous disposez désormais d'un remplacement pour RAISERROR, le JETER , ce qui facilite plus que jamais la capture des données d'erreur.

Dans cet article, nous allons voir le ESSAYEZ … bloc utilisé avec le RAISERROR et JETER déclarations. Les exemples sont basés sur un tableau que j'ai créé dans le AdventureWorks2012 exemple de base de données, sur une instance locale de SQL Server 2012. Le listing 1 montre le script T-SQL que j'ai utilisé pour créer le LastYearSales table.

Listing 1: Création de la table LastYearSales

Le script devrait être assez simple. J'utilise un CHOISIR… DANS pour récupérer les données de la Sales.vSalesPerson la visualiser et l'insérer dans la table nouvellement créée. Cependant, pour montrer comment gérer les erreurs, nous devons ajouter un élément de plus à notre table: une contrainte de vérification qui garantit la SalesLastYear la valeur n'est jamais inférieure à zéro. Le listing 2 montre le MODIFIER TABLE J'ai utilisé pour ajouter la contrainte.

Listing 2: Ajout d'une contrainte de vérification à la table LastYearSales

La contrainte facilite la génération d'une erreur lors de la mise à jour de la table. Tout ce que je dois faire, c'est essayer d'ajouter un montant négatif au SalesLastYear , un montant suffisamment élevé pour que SQL Server génère une erreur. Une fois que nous avons créé notre table et ajouté la contrainte de vérification, nous avons l'environnement dont nous avons besoin pour les exemples de cet article. Vous pouvez tout aussi facilement créer votre propre table et l'utiliser dans les exemples. Assurez-vous simplement d'avoir un moyen de violer une contrainte ou de trouver un autre mécanisme pour générer une erreur. Le but est de créer un script qui gère toutes les erreurs.

Une fois que nous avons configuré notre table, l'étape suivante consiste à créer une procédure stockée qui montre comment gérer les erreurs. La procédure, UpdateSales, modifie la valeur dans le SalesLastYear dans la colonne LastYearSales table pour un vendeur spécifié. Il fonctionne en ajoutant ou en soustrayant un montant de la valeur actuelle dans cette colonne. Le listing 3 montre le script que j'ai utilisé pour créer la procédure. Notez que j'inclus deux paramètres d'entrée-@SalesPersonID et @SalesAmt-qui coïncident avec la table SalesPersonID et SalesLastYear Colonnes.

Listing 3: Création d'une procédure stockée contenant un bloc Try… Catch

Le corps principal de la définition de la procédure, inclus dans le COMMENCER… FIN bloc, contient le ESSAYEZ … bloc, lui-même divisé en ESSAYER bloc et CAPTURE bloquer. le ESSAYER le bloc commence par DÉBUT et se termine par ENDTRY et contient le T-SQL nécessaire pour exécuter les actions de la procédure. Dans ce cas, j'inclus un MISE À JOUR déclaration qui ajoute le @Montant des ventes valeur à la SalesLastYear colonne. La déclaration est incluse dans DÉBUT DE TRANSACTION et COMMITTRANSACTION pour démarrer et valider explicitement la transaction. Les exemples varient en fonction de l'endroit où ils incluent les déclarations relatives aux transactions. (Certains n'incluent pas du tout les relevés.) Gardez simplement à l'esprit que vous souhaitez valider ou annuler vos transactions aux moments appropriés, selon qu'une erreur a été générée.

Si la MISE À JOUR s'exécute avec succès, le SalesLastYear est mise à jour et l'opération est terminée, auquel cas le code dans le CAPTURE le bloc n'est jamais exécuté. Cependant, si le MISE À JOUR échoue et SQL Server génère une erreur, la transaction est terminée et le moteur de base de données passe à la CAPTURE bloquer. le CAPTURE le bloc commence par BEGINCATCH et se termine par ENDCATCH et contient les instructions nécessaires pour gérer l'erreur.

Pour la procédure stockée dans le Listing 3, la première étape que je prends dans le CAPTURE le bloc consiste à annuler la transaction si elle est toujours en cours d'exécution. Je commence par utiliser le @@ TRANCOUNT pour déterminer si des transactions sont encore ouvertes. @@ TRANCOUNT est une fonction SQL Server intégrée qui renvoie le nombre de transactions en cours d'exécution dans la session en cours. Dans ce cas, il ne devrait y en avoir qu'un (si une erreur se produit), donc j'annule cette transaction.

Ensuite, je déclare un ensemble de variables basées sur les fonctions système que SQL Server met à disposition dans le cadre de la CAPTURE bloquer. Les fonctions renvoient des informations relatives aux erreurs que vous pouvez référencer dans vos instructions T-SQL. Actuellement, SQL Server prend en charge les fonctions suivantes à cette fin:

• ERROR_NUMBER (): Le numéro attribué à l'erreur.
• ERROR_LINE (): Numéro de ligne dans la routine qui a provoqué l'erreur.
• MESSAGE D'ERREUR(): Le texte du message d'erreur, qui inclut les valeurs fournies pour tous les paramètres substituables, tels que les heures ou les noms d'objets.
• ERROR_SEVERITY (): La gravité de l'erreur.
• ERROR_STATE (): Numéro d'état de l'erreur.
• ERROR_PROCEDURE (): Nom de la procédure stockée ou du déclencheur qui a généré l'erreur.

Pour cet exemple, j'utilise toutes les fonctions sauf la dernière, mais dans un environnement de production, vous pouvez également utiliser celle-ci.

Après avoir déclaré les variables, j'inclus deux IMPRESSION des instructions qui affichent les valeurs de la @ErrorNumber et @ErrorLine variables (avec un texte explicatif). La raison pour laquelle je fais cela est de démontrer la différence entre ce que sont les valeurs réelles et ce que le RAISERROR comme vous le verrez sous peu.

le RAISERROR déclaration vient après la IMPRESSION déclarations. L'instruction renvoie des informations d'erreur à l'application appelante. Généralement, lors de l'utilisation RAISERROR, vous devez inclure un message d'erreur, un niveau de gravité d'erreur et un état d'erreur. Les règles qui régissent le RAISERROR les arguments et les valeurs qu'ils renvoient sont un peu complexes et dépassent le cadre de cet article, mais pour les besoins de cet exemple, je passe simplement @Message d'erreur, @ErrorSeverity, et @ErrorState variables comme arguments.

C'est tout ce que vous devez faire pour créer une procédure stockée contenant un ESSAYEZ … bloquer. Dans un instant, nous allons essayer notre travail. Mais d'abord, récupérons une ligne de la LastYearSales tableau pour voir quelle est la valeur actuelle pour le vendeur 288. Le listing 4 montre le SÉLECTIONNER J'ai utilisé pour récupérer les données.

Listing 4: Récupération de la date dans la table LastYearSales

Sans surprise, la déclaration renvoie le nom et les ventes totales de ce vendeur, comme indiqué dans le listing 5. Comme vous pouvez le voir, Rachel Valdez affiche plus de 1,3 million de dollars de ventes pour l'année dernière.

Listing 5: Données extraites de la table LastYearSales

Essayons maintenant le UpdateSales procédure stockée. Juste pour le plaisir, ajoutons quelques millions de dollars aux totaux de Rachel Valdez. Le listing 6 montre comment j'utilise le EXEC déclaration pour appeler la procédure et transmettre l'ID du vendeur et les 2 millions de dollars.

Listing 6: Exécution de la procédure stockée UpdateSales

La procédure stockée doit s'exécuter sans problème car nous ne violons pas la contrainte de vérification. Si nous devions exécuter la SÉLECTIONNER à nouveau (celle de l'extrait 4), nos résultats ressembleraient à ceux de l'extrait 7. Notez tout l'argent supplémentaire.

Listing 7: Affichage du montant des ventes mis à jour dans le tableau LastYearSales

Voyons maintenant ce qui se passe si nous soustrayons suffisamment de son compte pour ramener ses totaux en dessous de zéro. Dans la liste 8, je lance à nouveau la procédure, mais cette fois précisez -4000000 pour le montant.

Listing 8: Causer la procédure stockée UpdateSales pour générer une erreur

Comme vous vous en souvenez, après avoir créé le LastYearSales table, j'ai ajouté une contrainte de vérification pour s'assurer que le montant ne pouvait pas tomber en dessous de zéro. Par conséquent, la procédure stockée génère maintenant une erreur, qui est indiquée dans le listing 9.

Listing 9: Le message d'erreur renvoyé par la procédure stockée UpdateSales

Comme prévu, les informations que nous avons incluses dans le CAPTURE bloc a été retourné. Mais notez que le numéro d'erreur réel (547) est différent du RAISERROR numéro de message (50000) et que le numéro de ligne réel (9) est différent du RAISERROR numéro de ligne (27). En théorie, ces valeurs devraient coïncider. Mais comme je l'ai mentionné plus tôt, les règles qui régissent RAISERROR sont un peu excentriques.

Pour simplifier le renvoi d'erreurs dans un CAPTURE bloc, SQL Server 2012 a introduit le JETER déclaration. Avec le JETER , vous n'avez pas besoin de spécifier de paramètres et les résultats sont plus précis. Vous incluez simplement la déclaration telle quelle dans le CAPTURE bloquer.

Pour démontrer la JETER déclaration, j'ai défini un ALTER PROCEDURE déclaration qui modifie la UpdateSales procédure, en particulier la CAPTURE bloc, comme indiqué dans l'extrait 10.

Listing 10: Modification de la procédure stockée UpdateSales

Notez que je conserve le @ErrorNumber et @ErrorLine déclarations de variables et leurs connexes IMPRESSION déclarations. Je le fais uniquement pour démontrer la JETER l'exactitude de la déclaration. En fait, je dois seulement annuler la transaction et spécifier le JETER , sans aucun paramètre.

Maintenant, exécutons à nouveau la procédure stockée, en essayant à nouveau de déduire 4 millions de dollars du montant des ventes, comme indiqué dans le listing 11.

Listing 11: Causer la procédure stockée UpdateSales pour générer une erreur

Une fois de plus, SQL Server renvoie une erreur. Seulement cette fois, les informations sont plus précises. Comme vous pouvez le voir dans l'extrait 12, les numéros de message et les numéros de ligne correspondent désormais. Nous n'avons plus besoin de déclarer des variables ou d'appeler des fonctions système pour renvoyer des informations relatives aux erreurs à l'application appelante.

Listing 12: Le message d'erreur renvoyé par la procédure stockée UpdateSales

Comme vous pouvez le voir, SQL Server 2012 rend la gestion des erreurs plus facile que jamais. Même si vous utilisez le ESSAYEZ … bloquer pendant un certain temps, le JETER déclaration devrait prouver un grand avantage sur RAISERROR. Et si vous débutez dans la gestion des erreurs dans SQL Server, vous constaterez que le ESSAYEZ … bloc et JETER ensemble, le processus est assez indolore, il vaut bien le temps et les efforts nécessaires pour les apprendre et les mettre en œuvre.