Procédures stockées en SQL | Un aperçu

Dans ce blog, nous allons discuter des procédures stockées dans SQL que vous pouvez utiliser pour enregistrer un ensemble de code et l'utiliser à plusieurs reprises chaque fois que vous en avez besoin. Les procédures stockées sont similaires aux vues. Cependant, vous pouvez effectuer des opérations de table courantes telles que DROP, TRUNCATE, DELETE, etc. avec des procédures stockées que vous ne pouvez pas effectuer avec des vues.

Les procédures stockées sont également pré-compilées afin qu'elles s'exécutent plus rapidement que les vues. Il minimise également la quantité de données envoyées au serveur de base de données.

Table des matières

• Syntaxe des procédures stockées en SQL : sans paramètres
• Syntaxe des procédures stockées en SQL : avec des paramètres
• Exemple de scénario de procédures stockées dans SSMS
• Deuxième exemple de scénario
• Troisième exemple de scénario
• Emplacement des procédures stockées enregistrées dans SQL
• Création d'une procédure stockée en SQL avec des paramètres
• Création d'une procédure stockée avec une valeur par défaut
• Conclusion

Syntaxe des procédures stockées en SQL : sans paramètres

Pour créer une procédure stockée sans paramètres, vous devez commencer par la fonction CREATE . Ajoutez ensuite la fonction PROC , tapez le nom de la procédure juste après et ajoutez la fonction AS .

La création d'une procédure stockée dans est similaire à la création de tables et de vues. Cependant, la différence réside dans le processus d'obtention des données.

Par exemple, si nous voulons obtenir des données à partir d'une vue, nous utilisons " SELECT * FROM view_name ". D'autre part, pour les procédures stockées, nous utilisons EXEC qui signifie "exécuter", puis nous tapons le nom de la procédure stockée après.

Une fois que nous exécutons une procédure stockée , elle affichera également les informations en fonction des instructions ou des commandes que vous avez ajoutées.

Syntaxe des procédures stockées en SQL : avec des paramètres

Passons à . Le processus est similaire à l'exemple précédent que j'ai démontré. La seule différence est que vous devez inclure les paramètres avant la fonction AS et également le type de données juste après.

Vous pouvez également utiliser le paramètre que vous avez déclaré lors de la création de la procédure stockée dans vos commandes. Cela vous permet de gagner du temps en saisissant plusieurs valeurs. Par exemple, au lieu de taper manuellement customerID1 , customerID2 , etc., nous avons utilisé @custid ou le paramètre pour éviter d'avoir un long ensemble de code.

Veuillez noter que lors de l'utilisation ou de l'inclusion d'un paramètre, il est important d'utiliser le symbole @ . Vous pouvez également inclure autant de paramètres que vous le souhaitez, pas un seul. C'est ainsi que nous exécutons des procédures stockées avec des paramètres.

Comme vous pouvez le constater, nous avons également utilisé le paramètre avec une valeur égale à 1 lors de l'exécution de la procédure stockée. Cela n'apportera que les informations de custid1 .

Exemple de scénario de procédures stockées dans SSMS

Prenons d'autres exemples de procédures stockées en SQL. Dans un premier temps, je vais créer un exemple de procédure stockée en utilisant la commande en surbrillance ci-dessous.

Pour la convention de dénomination lors de la création d'une procédure stockée, nous utilisons généralement "usp" ou "sp" pour indiquer qu'il s'agit d'une procédure stockée. Si vous vous demandez ce que signifie "usp", cela signifie simplement une procédure stockée définie par l'utilisateur. 

Ensuite, nous allons écrire des requêtes que la procédure stockée traitera lors de son exécution.

Les requêtes de l'exemple précédent supprimeront simplement la table nommée dbo.stageOrders . Ensuite, il recréera les dbo.stageOrders avec les données de la table Sales.SalesOrderHeader . 

Créons cette procédure stockée en mettant en surbrillance le code suivant et en cliquant sur le bouton Exécuter .

Après cela, vous devriez voir un message comme celui-ci.

Ensuite, nous allons maintenant exécuter la procédure stockée en exécutant le EXEC usp_TEST .

Lors de l'exécution de la procédure stockée usp_TEST , dbo.stageOrders doit maintenant contenir les données de Sales.SalesOrderHeader . Vérifions le contenu de dbo.stageOrders en exécutant la commande ci-dessous.

Par conséquent, ce sont les données que nous avons créées dans dbo.stageOrders en fonction des commandes que nous avons ajoutées à notre procédure stockée.

Deuxième exemple de scénario

Passons à un autre exemple. Cette fois, je ne veux pas supprimer la table dbo.stageOrders et recréer la même table avec des données provenant de la table Sales.SalesOrderHeader .

Au lieu de cela, je souhaite simplement que les données de la table Sales.SalesOrderHeader soient affichées chaque fois que j'exécute la procédure stockée " usp_TEST " .

Pour ce faire, je dois apporter des modifications à la procédure stockée récemment créée à l'aide de l' instruction ALTER .

Dans cet exemple, nous avons utilisé l' instruction ALTER qui est utilisée pour modifier notre procédure stockée. Vous pouvez également l'appliquer aux vues et aux tables. Une fois que nous avons exécuté le code dans la capture d'écran ci-dessus, cela devrait changer les requêtes à traiter par notre procédure stockée.

Cette fois, il ne recréera aucune table comme nous l'avons fait précédemment. Si nous exécutons « usp_TEST », cela apportera simplement les données de la table Sales.SalesOrderHeader .

Troisième exemple de scénario

Faisons un autre exemple. Cette fois, nous allons réutiliser la table dbo.stageOrders que nous avions dans notre premier exemple de création d'une procédure stockée dans SSMS . Nous allons commencer avec cet exemple en sélectionnant la table dbo.stageOrders. 

Les résultats lors de la sélection de toutes les données de dbo.stageOrders devraient ressembler à ceci.

Maintenant, vidons la table dbo.stageOrders en exécutant usp_TEST . Nous pouvons le faire en ajoutant la commande suivante.

Après avoir ajouté la commande TRUNCATE TABLE , nous devons mettre à jour usp_TEST .

Après cela, un message indiquant que les commandes sont terminées avec succès devrait s'afficher. Ensuite, nous exécuterons à nouveau usp_TEST .

Lors de l'exécution de usp_TEST , il affichera tous les enregistrements de Sales.SalesOrderHeader et videra également la table  dbo.stageOrders .

Pour vérifier si la table dbo.stageOrders est vide, nous devons la sélectionner à l'aide de la commande suivante et l'exécuter.

Lors de l'exécution du code ci-dessus, nous pouvons voir que la table dbo.stageOrders est maintenant vide. Cela est dû à la commande TRUNCATE TABLE que nous avons utilisée pour mettre à jour usp_TEST .

Emplacement des procédures stockées enregistrées dans SQL

Si vous voulez voir où la procédure stockée dans SQL est enregistrée, allez dans le panneau Explorateur d'objets sur le côté gauche et cliquez sur l'icône « + » devant la base de données sur laquelle vous travaillez.

Cliquez ensuite avec le bouton droit sur Programmabilité et sélectionnez Actualiser .

Développez le dossier ou le groupe Programmabilité en cliquant sur l'icône « + ». Développez ensuite le groupe Procédures stockées en procédant de la même manière. Dans le groupe Procédures stockées, vous devriez voir le dbo.usp_TEST .

Si vous souhaitez vérifier quelles commandes ou requêtes une procédure stockée spécifique dans SQL exécute, vous pouvez cliquer avec le bouton droit sur une procédure stockée et suivre les étapes de la capture d'écran ci-dessous.

Après cela, il ouvrira la procédure stockée dans un onglet différent où vous pourrez voir les commandes fournies. Voici à quoi ressemble dbo.usp_TEST lors de son ouverture.

Comme vous pouvez le voir, il existe des commandes par défaut avant l' instruction CREATE . Vous pouvez simplement le supprimer si vous le souhaitez. 

Vous savez maintenant comment vérifier les commandes exécutées par une procédure stockée dans SQL .

Création d'une procédure stockée en SQL avec des paramètres

Ensuite, nous allons créer une procédure stockée avec des paramètres. Par exemple, nous allons utiliser le code suivant pour créer une nouvelle procédure stockée.

Dans l'exemple de code, j'ai utilisé le même processus de création d'une procédure stockée nommée usp_GetCustomer . Ensuite, j'ai ajouté un paramètre qui est @CustomerID avec un type d'entrée de INT .

Notez qu'une fois que vous avez ajouté un paramètre lors de la création d'une procédure stockée, vous devez toujours fournir un paramètre chaque fois que vous allez exécuter une commande.

Voyons ce qui se passera si nous exécutons usp_GetCustomer sans fournir de paramètre.

Lors de l'exécution de usp_GetCustomer sans paramètre, un message d'erreur est apparu. Voici à quoi cela ressemblerait si nous exécutions usp_GetCustomer avec un paramètre.

Avec un paramètre fourni, nous pouvons obtenir un résultat correct lors de l'exécution de notre procédure stockée .

Création d'une procédure stockée avec une valeur par défaut

Si vous souhaitez éviter de recevoir un message d'erreur lors de l'exécution d'une procédure stockée avec un paramètre, vous pouvez définir une valeur par défaut qui servira de paramètre par défaut.

Par exemple, nous allons créer une procédure stockée nommée usp_GetOrdersByYear .

Ensuite, je vais ajouter un paramètre @OrderYear avec le type d'entrée " INT " et une valeur par défaut égale à 2011 .

Si nous exécutons usp_GetOrdersByYear sans paramètre fourni, il affichera les enregistrements avec l'année 2011 .

D'autre part, si nous exécutons usp_GetOrdersByYear avec 2014 comme paramètre donné, il devrait afficher les enregistrements avec l'année 2014 .

C'est ainsi que vous utilisez les procédures stockées dans vos tâches quotidiennes de gestion des données.

Clause HAVING dans les fonctions d'agrégation SQL
ISNULL Fonction SQL dans les instructions Case
Extraction de données SQL à l'aide de OFFSET et FETCH

Conclusion

En tenant compte de tout, vous avez appris ce qu'est une procédure stockée en SQL et son objectif. Nous avons également discuté de l'instruction ALTER qui est utilisée pour apporter des modifications ou des mises à jour dans une procédure stockée actuelle.

De plus, vous avez appris qu'il existe différentes méthodes pour créer des procédures stockées dans SQL et comment éviter de recevoir des erreurs lors de l'exécution d'une procédure stockée en fournissant une valeur par défaut.

Plus important encore, vous avez appris à utiliser des procédures stockées pour stocker des ensembles de commandes afin d'éviter d'exécuter de longs ensembles de code à plusieurs reprises. Enfin, n'oubliez pas d'utiliser le symbole « @ » lorsque vous fournissez un paramètre.

Tous mes vœux,

Hafiz