STORED PROCEDURES
Les STORED PROCEDURE sont des routines prenant en charge le flux avec plusieurs étapes. Ils prennent en charge de nombreux éléments T-SQL que les fonctions définies par l'utilisateur ne prennent pas, comme la modification des données dans la base de données et même l'application de modifications de définition de données aux objets de base de données (DLL), à l'aide de tables temporaires, de SQL dynamique, de gestion des erreurs,... Ce sont des avantages évidents par rapport aux fonctions définies par l'utilisateur, les STORED PROCEDURE ne peuvent pas être intégrées dans les requêtes.
Les STORED PROCEDURE prennent en charge les paramètres d'entrée et de sortie et peuvent également renvoyer des ensembles de résultats de requêtes. Le SQL Server met en cache les plans d'exécution des requêtes de la STORED PROCEDURE et les réutilise généralement dans les exécutions ultérieures de la procédure pour économiser le temps, les ressources de microprocesseur et mémoire associées à l'optimisation des requêtes.
Les STORED PROCEDURE offrent de nombreux avantages par rapport à l'implémentation de la logique métier dans l'application. Ils encapsulent la logique pour permettre la réutilisation et la dissimulation de la complexité. Il est beaucoup plus facile d'appliquer des modifications à une STORED PROCEDURE avec une simple commande ALTER PROC que de déployer des modifications dans l'application. De plus, avec les STORED PROCEDURE, vous avez tendance à avoir moins de trafic réseau car lorsque vous appelez une STORED PROCEDURE à partir de l'application, tout ce qui est transmis via le réseau n'est que le nom de la procédure et ses paramètres. Le flux s'exécute dans le moteur de base de données, puis seul le résultat final est envoyé à l'application. Lorsque vous implémentez la logique dans l'application, vous obtenez généralement plus d'allers-retours entre l'application et la base de données, et par conséquent plus de trafic réseau.
Les STORED PROCEDURE simplifient également la gestion de la sécurité dans la base de données. Souvent, vous ne souhaitez pas accorder aux utilisateurs des autorisations pour interroger et modifier directement les données dans les tables, mais vous souhaitez plutôt qu'ils puissent effectuer ces tâches uniquement directement via des STORED PROCEDURE. Pour ce faire, accordez aux utilisateurs des autorisations EXECUTE sur la STORED PROCEDURE sans leur accorder un accès direct aux objets sous-jacents.
Travailler avec des STORED PROCEDURES
Une STORED PROCEDURES est une routine réutilisable prenant en charge les paramètres d'entrée et de sortie, et renvoie même des ensembles de résultats de requêtes.
Supposons que l'on vous confie la tâche de créer une STORED PROCEDURES gérant ce que l'on appelle des conditions de recherche dynamique pour interroger et filtrer les données de la table Ventes.Commandes. La procédure doit avoir quatre paramètres d'entrée facultatifs pour filtrer la commande par d'identificateur de commande, date de commande, identificateur de client et identificateur d'employé. L'utilisateur exécutant détermine par quelle combinaison de colonnes filtrer. La façon dont vous rendez les paramètres d'entrée facultatifs est de les définir avec un NULL par défaut. Si l'utilisateur ne spécifie pas de valeur pour un paramètre d'entrée, il est défini sur la valeur NULL par défaut, vous indiquant que vous n'êtes pas censé appliquer un filtre à la colonne correspondante.
Exécutez le code suivant pour créer la STORED PROCEDURES DemandeCommande, gérant la tâche à accomplir :
- CREATE OR ALTER PROC dbo.DemandeCommande
- @NoCommande AS INT = NULL,
- @DateCommande AS DATE = NULL,
- @NoClient AS INT = NULL,
- @NoEmployee AS INT = NULL
-
- AS
-
- SET XACT_ABORT, NOCOUNT ON;
-
- SELECT NoCommande, DateCommande, DateExpedition, NoClient, NoEmployee, NoExpedition FROM dbo.Ventes.Commandes
- WHERE (NoCommande = @NoCommande OR @NoCommande IS NULL)
- AND (DateCommande = @DateCommande OR @DateCommande IS NULL)
- AND (NoClient = @NoClient OR @NoClient IS NULL)
- AND (NoEmployee = @NoEmployee OR @NoEmployee IS NULL);
-
- GO
Observez l'entête de la STORED PROCEDURE avec la définition des paramètres d'entrée et la syntaxe pour définir les valeurs par défaut. Certaines personnes aiment placer les définitions de paramètres entre parenthèses, comme dans :
- CREATE OR ALTER PROC dbo.DemandeCommande
- (
- @NoCommande AS INT = NULL,
- @DateCommande AS DATE = NULL,
- @NoClient AS INT = NULL,
- @NoEmployee AS INT = NULL
- )
- AS
En ce qui concerne la STORED PROCEDURE, notez qu'il n'y a pas de bloc BEGIN-END obligatoire comme dans les fonctions définies par l'utilisateur à plusieurs instructions, mais vous pouvez en utiliser un si c'est votre préférence de style, comme dans :
- BEGIN
- SET XACT_ABORT, NOCOUNT ON;
-
- SELECT NoCommande, DateCommande, DateExpedition, NoClient, NoEmployee, NoExpedition FROM dbo.Ventes.Commandes
- WHERE (NoCommande = @NoCommande OR @NoCommande IS NULL)
- AND (DateCommande = @DateCommande OR @DateCommande IS NULL)
- AND (NoClient = @NoClient OR @NoClient IS NULL)
- AND (NoEmployee = @NoEmployee OR @NoEmployee IS NULL);
- END;
Le code de la STORED PROCEDURE commence par définir l'option XACT_ABORT et NOCOUNT sur ON. L'option XACT_ABORT détermine l'effet des erreurs d'exécution générées par les instructions T-SQL. Lorsque cette option est désactivée (valeur par défaut dans la plupart des cas), certaines erreurs provoquent l'annulation d'une transaction ouverte et l'annulation de l'exécution du code, tandis que d'autres laissent la transaction ouverte. Pour obtenir un comportement plus fiable et cohérent, il est considéré comme une bonne pratique de définir cette option sur ON, et de cette façon, toutes les erreurs provoquent l'annulation d'une transaction ouverte et l'annulation de l'exécution du code. L'option NOCOUNT supprime les messages indiquant le nombre de lignes affectées par les instructions de manipulation de données. Lorsqu'il est désactivé (par défaut), ces messages peuvent dégrader les performances des requêtes en raison du trafic réseau qu'ils génèrent, ce qui cause des problèmes aux applications clientes les percevant comme des résultats de requêtes.
Le code appelle ensuite une requête sur la table Ventes.Commandes filtrant les données en fonction des paramètres spécifiés. Pour chaque paramètre, la clause WHERE de la requête a la disjonction de prédicats suivante (prédicats séparés par l'opérateur OR) :
- colonne = @parametre OR @parametre IS NULL
Lorsque l'utilisateur ne spécifie pas de valeur d'entrée, le paramètre est défini sur la valeur NULL par défaut. Dans un tel cas, le prédicat @parametre est NULL est vrai, et donc la disjonction des prédicats est vraie, donc aucun filtrage n'est appliqué. Lorsque l'utilisateur spécifie une valeur d'entrée, le prédicat @parametre IS NULL est faux et il appartient au prédicat colonne = @parameter de déterminer s'il faut conserver la ligne ou la supprimer.
Exécutez le code suivant pour tester la procédure, demandant de filtrer les commandes passées le 1 novembre 2021 par le client 74 :
- EXEC dbo.DemandeCommande @DateCommande = '20211101', @NoClient = 74;