Partager via

Comment : créer des procédures stockées et des fonctions définies par l'utilisateur

  • Article

Vous pouvez créer des procédures stockées à l'aide de l'Explorateur de serveurs. Les procédures stockées peuvent définir des règles d'entreprise complexes, contrôler la modification des données, limiter l'accès au moyen d'autorisations de sécurité, assurer l'intégrité des transactions et, d'une manière générale, effectuer sur la base de données les opérations demandées par votre application.

Microsoft SQL Server 2000 et les versions ultérieures vous permettent également d'utiliser l'Explorateur de serveurs pour créer des fonctions définies par l'utilisateur. Une fonction définie par l'utilisateur est une routine qui encapsule une logique utile en vue de son emploi dans d'autres requêtes. Alors que les vues sont limitées à une seule instruction SELECT, les fonctions définies par l'utilisateur peuvent comporter plusieurs instructions SELECT et donc fournir une logique plus puissante que celle obtenue au moyen des vues.

Les fonctions définies par l'utilisateur retournent toujours une valeur. Selon le type de valeur retourné, chaque fonction définie par l'utilisateur appartient à l'une des trois catégories suivantes :

  • **Fonction scalaire   **Fonction définie par l'utilisateur qui retourne une valeur scalaire, notamment un entier ou un horodatage. Si une fonction retourne une valeur scalaire, vous pouvez l'inclure dans une requête, partout où vous utiliseriez un nom de colonne.

  • Fonction inline   Si une fonction définie par l'utilisateur contient une seule instruction SELECT et si cette instruction peut être mise à jour, la table de résultats retournée par la fonction peut l'être également. De telles fonctions sont appelées fonctions inline. Lorsqu'une fonction inline retourne une table, vous pouvez l'utiliser dans la clause FROM d'une autre requête. Pour plus d'informations, consultez Comment : créer des requêtes qui utilisent autre chose qu'une table.

  • Fonction table   Si une fonction définie par l'utilisateur contient plusieurs instructions SELECT ou une instruction SELECT qui ne peut pas être mise à jour, la table de résultats retournée par cette fonction ne peut pas l'être non plus. Lorsqu'une fonction table retourne une table, vous pouvez l'utiliser dans la clause FROM d'une autre requête.

Notes

Dans les exemples suivants, dbo est l'acronyme de « database owner » (propriétaire de base de données) et désigne la procédure stockée et le nom de la fonction définie par l'utilisateur. La personne ainsi désignée est un utilisateur qui possède des autorisations implicites d'exécution de toutes les activités dans la base de données. N'importe quel objet créé par un membre du rôle sysadmin appartient automatiquement au dbo. Le qualificateur de nom dbo est inclus dans les exemples suivants.

Notes

Les boîtes de dialogue et les commandes de menu qui s'affichent peuvent être différentes de celles qui sont décrites dans l'aide, en fonction de vos paramètres actifs ou de l'édition utilisée. Pour modifier vos paramètres, choisissez Importation et exportation de paramètres dans le menu Outils. Pour plus d'informations, consultez Utilisation des paramètres.

Pour créer une procédure stockée

  1. Dans l'Explorateur de serveurs, cliquez avec le bouton droit sur le dossier Procédures stockées ou sur une procédure stockée de ce dossier.

  2. Dans le menu contextuel, sélectionnez Ajouter une nouvelle procédure stockée.

    Une nouvelle procédure stockée est créée dans l'éditeur de code source avec la structure d'instructions SQL suivante :

    CREATE PROCEDURE dbo.StoredProcedure1
/*
   (
      @parameter1 datatype = default value,
      @parameter2 datatype OUTPUT
   )
*/
AS
   /* SET NOCOUNT ON */
   RETURN

  3. Vous pouvez remplacer StoredProcedure1 à la première ligne par le nom de la nouvelle procédure. Par exemple, vous pouvez utiliser MyProcedure comme nom :

    CREATE PROCEDURE dbo.MyProcedure

    Notes

    Les procédures stockées doivent porter des noms uniques. Si vous choisissez un nom qui a déjà été assigné à une autre procédure stockée, un message d'erreur vous avertit qu'il existe déjà une procédure stockée qui porte ce nom.

  4. Écrivez la suite du texte de la procédure en SQL.

    Pour plus d'informations et des exemples de procédures stockées, consultez la documentation de votre serveur de base de données. Si vous utilisez Microsoft® SQL Server™, consultez « CREATE PROCEDURE » dans la documentation en ligne de SQL Server.

Pour créer une fonction définie par l'utilisateur

  1. Dans l'Explorateur de serveurs, cliquez avec le bouton droit sur le dossier Fonctions ou sur n'importe quelle autre fonction contenue dans ce dossier.

  2. Sélectionnez Ajouter nouveau, puis choisissez Fonction inline, Fonction table ou Fonction scalaire dans le menu contextuel.

    Notes

    Vous ne pouvez pas modifier la structure d'instructions SQL d'une nouvelle fonction en instructions SQL pour un autre type de fonction et enregistrer les résultats. Par exemple, si vous commencez par une fonction inline, vous ne pouvez pas modifier les instructions SQL en une fonction scalaire. L'enregistrement échouerait.

    Une nouvelle fonction définie par l'utilisateur est créée au moyen d'une structure d'instructions SQL. Par exemple, choisir Nouvelle fonction scalaire affiche la structure d'instructions SQL suivante :

    CREATE FUNCTION dbo.Function1
   (
   /*
   @parameter1 datatype = default value,
   @parameter2 datatype
   */
   )
RETURNS /* datatype */
AS
   BEGIN
      /* sql statement ... */
   RETURN /* value */
   END

  3. Vous pouvez remplacer Function1 à la première ligne par le nom de la nouvelle fonction. Par exemple, vous pouvez utiliser MyFunction comme nom :

    CREATE FUNCTION dbo.MyFunction

    Notes

    Les fonctions définies par l'utilisateur doivent porter des noms uniques. Si vous choisissez un nom qui a déjà été assigné à une autre fonction, un message d'erreur vous avertit qu'il existe déjà une fonction du même nom.

  4. Écrivez la suite du texte de la fonction en SQL.

Notes

Si le menu contextuel ne propose pas les options dont vous avez besoin, la version de Visual Studio que vous utilisez ne prend peut-être pas en charge cette fonctionnalité. Pour plus d'informations, consultez Éditions de Visual Database Tools.

Pour plus d'informations et des exemples de fonctions définies par l'utilisateur, consultez la documentation de votre serveur de base de données. Si vous utilisez Microsoft® SQL Server™, consultez « CREATE FUNCTION » dans la documentation en ligne de SQL Server.

Voir aussi

Concepts

Expressions dans des requêtes

Autres ressources

Utilisation de procédures stockées et de fonctions définies par l'utilisateur