Création de procédures stockées pour votre serveur SQL

Microsoft SQL Server fournit le mécanisme de procédure stockée pour simplifier le processus de développement de la base de données en regroupant les instructions Transact-SQL en blocs gérables. Les procédures stockées sont appréciées par la plupart des développeurs SQL Server qui considèrent que les avantages en termes d'efficacité et de sécurité qu'ils valent valent bien l'investissement initial dans le temps.

Avantages de l'utilisation de procédures stockées

Pourquoi un développeur devrait-il utiliser des procédures stockées?

Voici les principaux avantages de cette technologie:

• Exécution précompilée: SQL Server compile chaque procédure stockée une fois, puis réutilise le plan d'exécution. Il en résulte une augmentation considérable des performances lorsque des procédures stockées sont appelées à plusieurs reprises.
• Trafic client / serveur réduit: Si la bande passante réseau pose un problème dans votre environnement, vous serez ravi d'apprendre que les procédures stockées peuvent réduire les longues requêtes SQL à une seule ligne transmise par le réseau filaire.
• Réutilisation efficace du code et abstraction de la programmation: Les procédures stockées peuvent être utilisées par plusieurs utilisateurs et programmes clients. Si vous les utilisez de manière planifiée, vous constaterez que le cycle de développement prend moins de temps.
• Contrôles de sécurité améliorés: Vous pouvez accorder aux utilisateurs l'autorisation d'exécuter une procédure stockée indépendamment des autorisations de table sous-jacentes.

Les procédures stockées sont similaires aux fonctions définies par l'utilisateur, mais il existe des différences subtiles.

Structure

Les procédures stockées sont similaires aux constructions vues dans d'autres langages de programmation.

Ils acceptent les données sous la forme de paramètres d'entrée spécifiés au moment de l'exécution. Ces paramètres d'entrée (s'ils sont implémentés) sont utilisés dans l'exécution d'une série d'instructions qui produisent un résultat. Ce résultat est renvoyé à l'environnement appelant via l'utilisation d'un jeu d'enregistrements, de paramètres de sortie et d'un code de retour.

Cela peut sembler une bouchée, mais vous constaterez que les procédures stockées sont en réalité assez simples.

Exemple

Jetons un coup d'œil à un exemple pratique lié au tableau nommé inventaire présenté au bas de cette page. Ces informations sont mises à jour en temps réel et les responsables d’entrepôt vérifient en permanence les niveaux de produits stockés dans leurs entrepôts et disponibles pour expédition. Auparavant, chaque gestionnaire exécutait des requêtes semblables à celles-ci:

SÉLECTIONNER produit, quantitéÀ partir de l'inventaireWHERE Warehouse = 'FL'

Cela a entraîné des performances inefficaces sur le serveur SQL. Chaque fois qu'un responsable d'entrepôt exécutait la requête, le serveur de base de données était obligé de recompiler la requête et de l'exécuter à partir de zéro. Le responsable de l'entrepôt devait également avoir une connaissance de SQL et des autorisations appropriées pour accéder aux informations de la table.Au lieu de cela, le processus peut être simplifié grâce à l'utilisation d'une procédure stockée. Voici le code d'une procédure appelée sp_GetInventory qui récupère les niveaux de stock pour un entrepôt donné.

CREATE PROCEDURE sp_GetInventory@location varchar (10)COMMESÉLECTIONNER produit, quantitéÀ partir de l'inventaireWHERE Warehouse = @location

Le responsable de l'entrepôt de Floride peut ensuite accéder aux niveaux de stock en exécutant la commande suivante:

EXECUTE sp_GetInventory 'FL'

Le responsable de l'entrepôt de New York peut utiliser la même procédure stockée pour accéder à l'inventaire de cette zone:

EXECUTE sp_GetInventory 'NY'

Certes, ceci est un exemple simple, mais les avantages de l’abstraction sont visibles ici. Le responsable de l'entrepôt n'a pas besoin de comprendre le langage SQL ni le fonctionnement interne de la procédure. Du point de vue des performances, la procédure stockée fonctionne à merveille. SQL Server crée un plan d'exécution une fois, puis le réutilise en branchant les paramètres appropriés au moment de l'exécution.Maintenant que vous avez compris les avantages des procédures stockées, sortez et utilisez-les.

Essayez quelques exemples et mesurez les améliorations de performances obtenues. Vous serez surpris!

Tableau d'inventaire

ID	Produit	Entrepôt	Quantité
142	Haricots verts	New York	100
214	Pois	FL	200
825	Blé	New York	140
512	Haricots de Lima	New York	180
491	Tomates	FL	80
379	Pastèque	FL	85