Mise en route avec les procédures stockées dans SQL Server
par: Greg Robidoux | mise à jour: 2020-07-30 | commentaires (15) | connexes: Plus > procédures stockées
problème
j’utilise SQL Server depuis un certain temps, mais tout le code émis contre la base de données est intégré dans le code de l’application. Je sais que vous pouvez créer des procédures stockées, mais je ne sais pas exactement par où commencer ou ce dont j’ai besoin pour implémenter des procédures stockées.,
Solution
Les procédures stockées ne sont rien de plus qu’un lot d’instructions T-SQL stockées dans la base de données. Au lieu d’avoir à émettre plusieurs instructions fromyour application, vous pouvez émettre une commande pour appeler la procédure stockée pour effectuer un travail au lieu d’une seule instruction. De plus, puisque le code est stocké dans la base de données, vous pouvez émettre le même ensemble de code encore et encore à partir de différentes applications ou d’une fenêtre de requête. Pour commencer, le reste de cette astuce examine certainséchantillons de procédures stockées et comment vous pouvez commencer et les développer.,
Les exemples ci-dessous vous montrent à quel point il est simple de créer des procédures stockées. Tous ces exemples utilisent la base de données AdventureWorks, mais ces exemples doivent être assez simples pour que vous puissiez appliquer ces concepts à vos propres bases de données et applications.
exemple 1 – procédure stockée simple
Ce premier exemple crée une procédure stockée simple qui obtient l’enregistrement 1 supérieur de la personne.Table Contact.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
après la création de ce qui précède, utilisez la commande ci-dessous pour exécuter cette procédure de stockage.,
EXEC uspGetContact
C’est les résultats de cette première requête.
Exemple 2 – procédure stockée avec un paramètre
l’exemple suivant est une modification du premier exemple, mais cette fois addinga paramètre qui est passé dans la procédure de sélectionner de façon dynamique les enregistrements.Au lieu d’utiliser CREATE PROCEDURE, nous utilisons ALTER PROCEDURE pour modifier la procédure que nous avons créée dans L’exemple 1 au lieu de la supprimer d’abord, puis de la recréer.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
ci-Dessous montre deux façons différentes la procédure stockée peut être exécuté. Le premier examplejust passe la valeur de paramètre que nous voulons utiliser et le deuxième exemple inclut également le nom de paramètre avec la valeur. Vous pouvez exécuter la procédure stockée avec soitune de ces commandes.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
C’est les résultats de cette première requête.,
Exemple 3 – procédure stockée avec un paramètre et le paramètre de sortie
Dans cet exemple, nous avons à la fois un paramètre d’entrée ainsi que d’un paramètre de SORTIE.Le paramètre output sera utilisé pour renvoyer le ContactID que nous recherchons dans la procédure stockée. Ce paramètre de sortie sera ensuite utilisé pour sélectionner Thepersons ContactID, FirstName et LastName ainsi que tous les enregistrements d’adresse pour theperson.,
encore une fois, nous modifions la procédure stockée uspGetContact, puis Deuxièmement, nous exécutons le prochain ensemble de code qui exécute la procédure uspGetContact et ensuite, basé sur la valeur de retour qu’il obtient, il interrogera également le nom des personnes et addressinfo.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
Après la procédure stockée a été modifié exécuter le bloc de code ci-dessous. Ceci exécutera la procédure stockée ci-dessus et si le ContactID a une valeur, il retournera également les informations sur la personne et l’adresse.
DECLARE @ContactID INT SET @ContactID = 0 EXEC uspGetContact @LastName="Smith", @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END
C’est les résultats.,
exemple 4 – procédure stockée utilisant L’instruction RAISERROR
dans cet exemple, nous combinons les deux étapes de L’exemple 3 en une seule procédure stockée.La première étape consiste à obtenir le ContactID, puis la deuxième partie de la procédure recherchera les informations de nom et d’adresse des personnes. Nous avons également ajouté dans le code pour utiliser l’instruction theRAISERROR pour renvoyer une erreur si aucun enregistrement n’est trouvé.
Ceci est ensuite exécuté deux fois pour montrer à quoi il ressemble lorsque des données sont trouvées etquand aucune donnée n’est trouvée., L’instruction RAISERROR peut être utilisée pour contrôler la façon dont votre applicationhandles données ou toute autre erreur qui peut se produire.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName IF @@ROWCOUNT > 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
C’est les résultats.
EXEC uspGetContact @LastName="Job"
Ce sont les résultats lorsque aucune donnée n’est trouvée.
Exemple 5 – procédure stockée avec un appel de procédure stockée
Voici un autre exemple où nous avons deux procédures stockées., Le premier storedprocedure uspfindcontact recherche le premier enregistrement qui a un enregistrement d’adresse et retourne ensuite le ContactID à la procédure stockée appelante pour afficher à nouveau les informations de personne et d’adresse.
CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
le code ci-dessous modifie la procédure stockée uspGetContact qui appelleuspfindcontact et renvoie les jeux d’enregistrements.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SET @ContactID = 0 EXEC uspFindContact @, @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
C’est les résultats.
EXEC uspGetContact @LastName="Job"
C’est les résultats.,
exemple 6 – procédure stockée avec des commentaires
ce dernier exemple prend la procédure stockée uspGetContact et ajoute des commentaires au code afin que vous puissiez voir comment les commentaires fonctionnent dans une procédure stockée.
les commentaires peuvent être faits de deux façons
- En utilisant —
- En Utilisant / * pour commencer le bloc de commentaires et * / pour terminer le bloc de commentaires.
d’Autres que rien d’autre n’a changé dans la procédure stockée.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS /* This is a sample stored procedure to show how comments work within a stored procedure */ -- declare variable DECLARE @ContactID INT -- set variable value SET @ContactID = 0 -- execute stored proc and return ContactID value EXEC uspFindContact @, @ OUTPUT -- if ContactID does not equal 0 then return data else return error IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
Ce sont des exemples assez simples, mais j’espère que cela vous donnera une idée de la facilité avec laquelle il est possible de créer des procédures stockées pour SQL Server. Si vous pouvez exécuter un statementfrom SELECT soit une fenêtre de requête ou de votre application, vous pouvez tout aussi facilement exécuter une procédure astored comme indiqué ci-dessus.,
prochaines étapes
- Si vous n’utilisez pas déjà les procédures stockées, j’espère que cela vous donnera un aperçu de ce que vous devez faire pour commencer à les utiliser
- comme mentionné, ce sont des exemples assez simples, mais à peu près tout ce que vous pouvez faire avec un lot d’instructions peut être combiné dans une procédure stockée,
- découvrez thestored procédure tutoriel pour plus d’exemples
Dernière mise à Jour: 2020-07-30
a Propos de l’auteur
Greg Robidoux est le Président de Edgewood Solutions et co-fondateur de MSSQLTips.com.Afficher tous mes conseils
- Plus de Développeur de Base de données Conseils…