SQL Server

Procédures stockées dans SQL Server : introduction pour débutants

Apprenez à créer, exécuter, paramétrer et modifier une procédure stockée simple dans SQL Server, puis comparez-la à une vue.

Par JaviPublié 12 min de lectureDébutant

Une procédure stockée est un objet nommé de la base qui contient une ou plusieurs instructions SQL. Une application ou une personne peut l’exécuter par son nom et fournir des valeurs d’entrée au lieu de reconstruire une requête à chaque appel. Les procédures servent souvent à fournir une opération cohérente, centraliser du SQL répété et contrôler les opérations qu’un compte applicatif peut effectuer.

Cette leçon crée dbo.usp_GetOrdersByCustomer, qui renvoie les commandes d’un client dans la base synthétique JaviBuildsLab. Elle s’appuie sur SELECT, WHERE et sur l’idée de requête réutilisable présentée avec les vues. Une procédure n’est pas automatiquement plus rapide, plus sûre ou meilleure qu’une requête bien conçue ; son intérêt dépend de son usage et de sa maintenance.

Créer une procédure stockée simple

Dans SSMS, connectez-vous à la base voulue et créez cette procédure :

Requête SQL: Créer une procédure stockée simpleT-SQL
CREATE PROCEDURE dbo.usp_GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, CustomerId, OrderDate, Status, Amount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC, OrderId DESC;
END;
T-SQL11 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Le schéma dbo fait partie du nom. Le préfixe usp_ est une convention parfois utilisée pour les procédures créées par les équipes ; SQL Server ne l’impose pas. @CustomerId int déclare un paramètre d’entrée nommé @CustomerId dont la valeur doit être un entier. La requête utilise cette valeur dans son filtre. SET NOCOUNT ON supprime le message supplémentaire du nombre de lignes affectées par les instructions de la procédure, ce qui peut simplifier le traitement du résultat côté client.

Le corps se trouve entre BEGIN et END. Cet exemple ne contient qu’un SELECT, mais une procédure peut inclure plusieurs instructions. Gardez les premières procédures ciblées afin que leurs entrées, leur résultat et leurs actions soient faciles à comprendre. ORDER BY définit l’ordre renvoyé par ce SELECT ; il ne constitue pas une propriété permanente des données.

Dans SSMS, exécutez CREATE dans la base JaviBuildsLab. CREATE PROCEDURE doit être la première instruction de son lot. Si d’autres commandes le précèdent, séparez les lots avec GO ou créez la procédure dans une autre fenêtre. Dans l’Explorateur d’objets, développez Programmability, puis Stored Procedures, et actualisez le nœud pour voir dbo.usp_GetOrdersByCustomer.

Exécuter la procédure

Appelez-la avec EXEC (ou le mot-clé complet EXECUTE) et fournissez un identifiant de client :

Requête SQL: Exécuter la procédureT-SQL
EXEC dbo.usp_GetOrdersByCustomer @CustomerId = 11;
T-SQL1 ligneAffichage en lecture seule. Aucune connexion à une base de données.

La forme nommée indique clairement quel paramètre reçoit la valeur. SQL Server exécute le SELECT avec 11 comme valeur de @CustomerId et renvoie les commandes de ce client. Essayez un autre identifiant. Si le client n’a aucune commande correspondante, le résultat est vide ; cela ne signifie pas que la procédure a échoué.

Les paramètres permettent de réutiliser la même procédure avec des entrées différentes. L’instruction SQL reste fixe tandis que la valeur varie. C’est plus facile à examiner qu’une nouvelle requête construite par concaténation de chaînes et cela évite d’interpréter une entrée utilisateur comme du code SQL. Le code applicatif devrait quand même utiliser les paramètres fournis par le pilote de base de données au lieu d’insérer des valeurs dans le texte SQL. Cet exemple reste volontairement sur un paramètre entier ; le SQL dynamique avancé n’est pas abordé.

Si le paramètre n’est pas fourni et qu’aucune valeur par défaut n’est définie, SQL Server signale qu’il manque. Une procédure peut définir une valeur par défaut, mais seulement si elle a une signification métier claire. N’en ajoutez pas uniquement pour supprimer l’erreur : une valeur involontaire peut renvoyer de mauvaises données ou élargir une opération.

Lire et tester le résultat

Examinez les lignes pour CustomerId = 11 et comparez-les à un SELECT direct utilisant le même filtre. Testez un client qui a plusieurs commandes et, s’il en existe un dans l’échantillon, un client sans commande. Vérifiez le résultat utile ainsi que le comportement lorsque rien ne correspond.

Le nom de la procédure indique son intention, mais ne garantit pas l’exactitude de sa sortie. Si la table évolue, la procédure devra peut-être évoluer également. Les noms et types des paramètres et les colonnes de sortie forment une interface pour les consommateurs. Choisissez-les avec soin et retestez la procédure après toute modification.

Cet exemple en lecture seule ne modifie pas de lignes. D’autres procédures peuvent insérer, modifier ou supprimer des données. Lisez leur définition et confirmez leurs effets avant d’exécuter une procédure inconnue, surtout sur une base partagée ou de production. Respectez le processus de revue de votre équipe.

Modifier une procédure avec ALTER PROCEDURE

Utilisez ALTER PROCEDURE pour remplacer la définition tout en conservant l’identité de l’objet. Cette variante renvoie aussi le nom du client en joignant dbo.Customers :

Requête SQL: Modifier une procédure avec ALTER PROCEDURET-SQL
ALTER PROCEDURE dbo.usp_GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT c.CustomerId, c.FirstName, c.LastName,
           o.OrderId, o.OrderDate, o.Status, o.Amount
    FROM dbo.Customers AS c
    INNER JOIN dbo.Orders AS o
        ON o.CustomerId = c.CustomerId
    WHERE c.CustomerId = @CustomerId
    ORDER BY o.OrderDate DESC, o.OrderId DESC;
END;
T-SQL14 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Exécutez toute la définition ALTER, puis appelez la procédure et comparez ses colonnes et ses lignes aux attentes des consommateurs. Une modification de forme du résultat peut toucher les applications, rapports et autres procédures. Les noms et types des paramètres font également partie de l’interface ; coordonnez les changements si d’autres appels en dépendent.

Procédure ou vue ?

Une vue est une requête nommée que l’on lit avec SELECT. Elle sert souvent à exposer un résultat partagé, tel que celui de dbo.vw_CustomerOrders. Une procédure s’appelle avec EXEC, peut accepter des paramètres, renvoyer un résultat et effectuer d’autres opérations. Une vue sert de source de requête ; une procédure est une opération appelée.

Choisissez une vue si le besoin principal est de fournir une requête réutilisable que les appelants peuvent filtrer ou joindre. Choisissez une procédure si les appelants ont besoin d’une action nommée avec des entrées définies ou d’une suite d’instructions. Les deux peuvent centraliser du SQL, mais ne doivent pas masquer une logique que les personnes responsables ne comprennent pas. Consultez Les vues dans SQL Server.

Les autorisations en bref

Les autorisations déterminent qui peut exécuter une procédure et qui peut lire ou modifier les tables sous-jacentes. Un administrateur peut accorder l’autorisation EXECUTE à un utilisateur ou à un rôle applicatif. Le choix exact dépend des propriétaires, des schémas, du déploiement et des règles de l’organisation ; le responsable de la base doit l’examiner.

Ne supposez pas que le SQL devient automatiquement sûr parce qu’il est placé dans une procédure. La conception doit être appropriée et les permissions configurées. Évitez les autorisations étendues, par exemple le rôle de propriétaire de base, simplement pour faire fonctionner un exemple. Pour l’apprentissage, exécutez la procédure avec votre compte local de pratique dans JaviBuildsLab.

Performance et maintenance

Une procédure stockée n’est pas automatiquement plus rapide qu’une requête équivalente. Elle peut améliorer la réutilisation et la cohérence, mais SQL Server doit toujours exécuter les instructions sur les données disponibles. Les performances dépendent de la requête, du schéma, des index, du volume et de la charge. Commencez par rendre la logique correcte et compréhensible ; mesurez les performances réelles lorsqu’un besoin existe.

Une procédure ne remplace pas une bonne conception de requête. Une jointure confuse, un filtre absent ou des colonnes superflues restent des problèmes lorsqu’ils sont enveloppés dans une procédure. Choisissez des types utiles pour les paramètres, ne renvoyez que les colonnes nécessaires et documentez les hypothèses importantes. N’ajoutez pas de couches procédurales sans raison claire.

Exercice et suite

Créez la procédure dans la base de pratique, exécutez-la avec le client 11 et comparez son résultat à un SELECT direct. Modifiez-la pour inclure le nom du client, relancez-la et notez les changements. Avant d’utiliser une procédure écrite par quelqu’un d’autre, examinez sa définition et déterminez si elle lit ou modifie les données.

  1. L’appelant fournit @CustomerId
  2. EXEC la procédure stockée
  3. SELECT renvoie les commandes
Appeler une procédure paramétrée

Une procédure regroupe une opération nommée. La prochaine leçon étudie les clés qui relient les clients aux commandes et aident SQL Server à protéger ces relations : clés primaires et étrangères. Vous pouvez continuer sur la feuille de route SQL Server Foundations.

Vérifiez ce que vous avez retenu

2 questions rapides

Question 1 sur 2Qu’est-ce qu’une procédure stockée ?
Question 2 sur 2Comment une procédure peut-elle recevoir des données ?

Fondamentaux de SQL Server

Leçon 22 sur 25

Continuer vers la leçon suivante

Clés primaires et clés étrangères dans SQL Server

Continuer vers la leçon 23

Étiquettes

  • SQL Server
  • Fondamentaux SQL
  • SSMS
  • T-SQL