Créer des procédures stockées
Les procédures stockées sont l’un des outils les plus puissants de SQL Server pour encapsuler la logique métier et améliorer les performances des applications. Lorsque vous créez des procédures stockées, vous créez des blocs de code réutilisables qui s’exécutent sur le serveur, ce qui réduit le trafic réseau et centralise la logique d’accès aux données.
Comprendre les procédures stockées
Une procédure stockée est une collection compilée d’instructions T-SQL que SQL Server stocke et exécute en tant qu’unité unique. Contrairement aux requêtes non planifiées que vous envoyez au serveur à chaque fois, les procédures stockées sont précompilées et optimisées, ce qui signifie qu’elles s’exécutent plus rapidement sur les exécutions suivantes.
Vous utilisez des procédures stockées pour encapsuler une logique métier complexe, appliquer des règles de validation des données et contrôler la façon dont les applications interagissent avec votre base de données. Par exemple, au lieu d’autoriser l’accès direct aux tables, vous pouvez créer des procédures stockées qui valident les entrées, appliquent des règles métier et journaliser les modifications avant de modifier des données.
Les avantages en matière de performances proviennent de la mise en cache du plan de requête. Avec des requêtes non planifiées, SQL Server doit analyser et optimiser chaque requête à chaque fois. Avec les procédures stockées, le plan d’exécution est mis en cache après la première exécution, ce qui réduit la surcharge pour les opérations répétées.
Créer des procédures stockées de base
La création d’une procédure stockée commence par l’instruction CREATE PROCEDURE suivie de votre logique T-SQL. Vous spécifiez le nom de la procédure à l’aide d’un identificateur qualifié de schéma, ce qui améliore la clarté et les performances.
CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;
END
L’instruction SET NOCOUNT ON empêche le message concernant le nombre de lignes affectées d’être envoyées au client. Cela réduit le trafic réseau et améliore les performances, en particulier lorsque la procédure exécute plusieurs instructions.
Lorsque vous créez des procédures, utilisez les mots clés BEGIN et END pour définir clairement le corps de la procédure. Cela rend votre code plus lisible et permet d’éviter les erreurs lors de l’ajout ou de la modification de la logique ultérieurement.
Utiliser des paramètres
Les paramètres rendent les procédures stockées flexibles et réutilisables. Vous définissez les paramètres d’entrée pour accepter les valeurs de l’application appelante et les paramètres de sortie pour renvoyer des valeurs à l’appelant.
Les paramètres d’entrée utilisent le symbole @ suivi d’un nom de paramètre et d’un type de données. Vous pouvez fournir des valeurs par défaut pour rendre les paramètres facultatifs :
CREATE PROCEDURE dbo.GetCustomerOrdersByDate
@CustomerID int,
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
OrderDate,
TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
AND (@StartDate IS NULL OR OrderDate >= @StartDate)
AND (@EndDate IS NULL OR OrderDate <= @EndDate)
ORDER BY OrderDate DESC;
END
Les paramètres de sortie vous permettent de retourner des valeurs à l’application appelante. Vous les définissez à l’aide du OUTPUT mot clé :
CREATE PROCEDURE dbo.CalculateOrderTotal
@OrderID int,
@TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @TotalAmount = SUM(Quantity * UnitPrice)
FROM dbo.OrderDetails
WHERE OrderID = @OrderID;
RETURN 0;
END
Lorsque vous appelez une procédure avec des paramètres de sortie, vous devez déclarer une variable pour recevoir la valeur et utiliser le OUTPUT mot clé dans l’instruction EXECUTE .
Implémenter la gestion des erreurs
Les procédures stockées robustes incluent la gestion des erreurs pour gérer des conditions inattendues et maintenir l’intégrité des données. Vous implémentez la gestion des erreurs à l’aide TRY...CATCH de blocs, qui fonctionnent de la même façon que la gestion des exceptions dans d’autres langages de programmation.
CREATE PROCEDURE dbo.InsertCustomerOrder
@CustomerID int,
@OrderDate datetime,
@TotalAmount decimal(10,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Validate customer exists
IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
BEGIN
RAISERROR('Customer does not exist.', 16, 1);
END
-- Insert order
INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
VALUES (@CustomerID, @OrderDate, @TotalAmount);
COMMIT TRANSACTION;
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity int = ERROR_SEVERITY();
DECLARE @ErrorState int = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN -1;
END CATCH
END
Le TRY bloc contient votre logique principale, tandis que le CATCH bloc gère les erreurs qui se produisent. Vous pouvez utiliser des fonctions système telles que ERROR_MESSAGE(), ERROR_SEVERITY()et ERROR_STATE() pour capturer les détails d’erreur et les transmettre à l’application appelante.
Toujours vérifier @@TRANCOUNT avant d'annuler des transactions dans le bloc CATCH. Cela empêche les erreurs si la transaction est déjà terminée ou n’a jamais été démarrée.
Appliquer les meilleures pratiques
Le suivi des meilleures pratiques établies lorsque vous créez des procédures stockées garantit qu’elles sont maintenables, sécurisées et performantes.
Utiliser des noms qualifiés de schéma
Utilisez des noms qualifiés de schéma pour tous les objets. Cela élimine l’ambiguïté et améliore les performances en évitant la surcharge de résolution de schéma :
-- Good
SELECT * FROM dbo.Orders
-- Avoid
SELECT * FROM Orders
Implémenter la validation des paramètres
Implémentez la validation des paramètres au début de votre procédure. Interrompez rapidement lorsque les entrées ne sont pas valides, plutôt que de traiter des données incorrectes.
IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
RAISERROR('CustomerID must be a positive integer.', 16, 1);
RETURN -1;
END
Évitez SELECT *
Évitez SELECT * dans le code de production. Répertoriez explicitement les colonnes pour éviter les problèmes lorsque les structures de table changent et pour améliorer les performances des requêtes :
-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders
-- Avoid
SELECT * FROM dbo.Orders
Utiliser des noms explicites
Utilisez des noms explicites qui décrivent ce que fait la procédure. Incluez un verbe qui indique l’opération (Get, Insert, Update, Delete, Calculate) :
CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders
Éviter le sp_ préfixe
N’utilisez pas le préfixe sp_ pour vos procédures stockées. SQL Server réserve ce préfixe pour les procédures système stockées dans la master base de données. Lorsque vous nommez une procédure avec sp_, SQL Server effectue d’abord des recherches master avant de vérifier la base de données active, en ajoutant une surcharge inutile :
-- Good
CREATE PROCEDURE dbo.GetCustomerOrders
-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders
L’élaboration de ces pratiques vous permet de créer des procédures stockées que votre équipe peut comprendre, gérer et approuver pour effectuer des performances fiables dans les environnements de production.