Exécuter une procédure stockée

S’applique à : SQL Server (toutes les versions prises en charge) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Cet article explique comment exécuter une procédure stockée dans SQL Server en utilisant SQL Server Management Studio ou Transact-SQL.

Il existe deux façons différentes d'exécuter une procédure stockée. La première approche, et aussi la plus courante, est qu'une application ou un utilisateur appelle la procédure. La deuxième méthode consiste à définir la procédure pour qu'elle s'exécute automatiquement lorsqu'une instance de SQL Server démarre. Lorsqu’une procédure est appelée par une application ou un utilisateur, le mot clé Transact-SQL EXECUTE ou EXEC est explicitement établi dans l’appel. La procédure peut être appelée et exécutée sans le mot clé EXEC si elle est la première instruction du lot Transact-SQL.

Avant de commencer

Limitations et restrictions

Le classement de la base de données d'appel est utilisé pour mettre en correspondance les noms des procédures système. Pour cette raison, utilisez systématiquement la casse exacte des noms des procédures système dans vos appels de procédure. Par exemple, le code suivant ne fonctionnera pas s’il est exécuté dans le contexte d’une base de données dotée d’un classement qui respecte la casse :

EXEC SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help  

Pour afficher les noms exacts des procédures système, interrogez les affichages catalogue sys.system_objects et sys.system_parameters .

Si une procédure définie par l'utilisateur a le même nom qu'une procédure système, elle peut ne jamais s'exécuter.

Recommandations

Exécuter des procédures stockées système

Les procédures système commencent par le préfixe sp_. Étant donné qu'elles figurent logiquement dans toutes les bases de données d'utilisateur et les bases de données définies par le système, elles peuvent être exécutées à partir de n'importe quelle base de données sans devoir qualifier entièrement le nom de la procédure. Cependant, nous vous conseillons de qualifier tous les noms de procédures système à l’aide du nom de schéma sys pour éviter les conflits de noms. L'exemple suivant illustre la méthode recommandée pour l'appel d'une procédure système.

EXEC sys.sp_who;  

Exécuter des procédures stockées définies par l’utilisateur

En exécutant une procédure définie par l'utilisateur, il est recommandé de qualifier le nom de la procédure avec le nom du schéma. Cette pratique améliore légèrement les performances car le Moteur de base de données n'a pas à rechercher dans plusieurs schémas. Elle évite également d'exécuter la procédure incorrecte si une base de données a des procédures de même nom dans plusieurs schémas.

L'exemple suivant illustre la méthode recommandée pour l'exécution d'une procédure définie par l'utilisateur. Notez que la procédure accepte un paramètre d'entrée. Pour plus d’informations sur la spécification des paramètres d’entrée et de sortie, consultez Spécifier les paramètres.

USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;
GO

-Ou-

EXEC AdventureWorks2019.dbo.uspGetEmployeeManagers 50;  
GO  

Si une procédure non qualifiée définie par l'utilisateur est spécifiée, le Moteur de base de données la recherche dans l'ordre suivant :

  1. Schéma sys de la base de données active.

  2. Schéma par défaut de l'appelant s'il est exécuté dans un traitement ou en SQL dynamique, ou bien, si le nom non qualifié de la procédure apparaît dans le corps d'une autre définition de procédure, le schéma contenant cette autre procédure est recherché par la suite.

  3. Schéma dbo dans la base de données active.

Exécuter des procédures stockées automatiquement

Les procédures marquées pour l’exécution automatique sont exécutées chaque fois que SQL Server démarre et que la base de données master est récupérée pendant le processus de démarrage. Configurer des procédures pour qu'elles s'exécutent automatiquement peut être utile pour effectuer des opérations de maintenance de base de données ou pour exécuter les procédures en continu en tant que processus d'arrière-plan. L’utilisation de l’exécution automatique permet également aux procédures d’exécuter des tâches système ou de maintenance dans tempdb, comme la création d’une table temporaire globale. De cette façon, la table temporaire sera conservée après la recréation de tempdb pendant le démarrage de SQL Server.

Une procédure exécutée automatiquement opère avec les mêmes droits que les membres du rôle de serveur sysadmin . Les messages d'erreur produits par une procédure sont enregistrés dans le journal des erreurs de SQL Server .

Le nombre de procédures au démarrage est illimité, mais gardez à l'esprit que chacune utilise un thread de travail lors de son exécution. Si vous devez exécuter plusieurs procédures au démarrage mais si vous ne devez pas les exécuter de manière parallèle, utilisez comme procédure de démarrage une des procédures qui appellera les autres. Cette démarche permet de n'utiliser qu'un seul thread de travail.

Conseil

Une procédure en exécution automatique ne retourne pas de jeu de résultats. En effet, la procédure est exécutée par SQL Server et non par une application ou un utilisateur et il n'y a pas d'emplacement où envoyer le jeu de résultats.

Activer, désactiver et contrôler l’exécution automatique

Seul l’administrateur système (sa) peut marquer une procédure de sorte qu’elle s’exécute automatiquement. En outre, la procédure doit se trouver dans la base de données master, elle doit être la propriété de sa et elle ne peut pas avoir de paramètres d’entrée ou de sortie.

Utilisez sp_procoption pour :

  1. désigner une procédure existante comme procédure de démarrage ;

  2. supprimer l'exécution automatique d'une procédure au démarrage de SQL Server .

Sécurité

Pour plus d’informations, consultez EXECUTE AS (Transact-SQL) et EXECUTE AS Clause (Transact-SQL).

Autorisations

Pour plus d’informations, consultez la section « Autorisations » dans EXECUTE (Transact-SQL).

Utilisation de SQL Server Management Studio

Exécuter une procédure stockée

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données SQL Server, développez cette instance, puis développez Bases de données.

  2. Développez la base de données que vous souhaitez, développez Programmabilité, puis développez Procédures stockées.

  3. Cliquez avec le bouton droit sur la procédure stockée définie par l’utilisateur que vous souhaitez exécuter, puis sélectionnez Exécuter la procédure stockée.

  4. Dans la boîte de dialogue Exécuter la procédure , entrez une valeur pour chaque paramètre et indiquez si le paramètre doit passer une valeur Null.

    Paramètre
    Indique le nom du paramètre.

    Type de données
    Indique le type de données du paramètre.

    Paramètre de sortie
    Indique si le paramètre est un paramètre de sortie.

    Passer les valeurs de type NULL
    Permet le passage d'une valeur NULL en tant que valeur du paramètre.

    Valeur
    Tapez la valeur du paramètre lors de l'appel de la procédure.

  5. Pour exécuter la procédure stockée, sélectionnez OK.

Utilisation de Transact-SQL

Exécuter une procédure stockée

  1. Connectez-vous au Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple montre comment exécuter une procédure stockée qui attend un seul paramètre. L’exemple exécute la procédure stockée uspGetEmployeeManagers avec 6 comme valeur du paramètre @EmployeeID.

USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO  

Activer ou désactiver l’exécution automatique d’une procédure

Les procédures de démarrage doivent se trouver dans la base de données master et ne peuvent pas contenir de paramètres d’entrée ou de sortie. L'exécution des procédures stockées démarre lorsque toutes les bases de données sont récupérées et le message « Récupération terminée » est enregistré au démarrage.

Pour plus d’informations, consultez sp_procoption (Transact-SQL).

  1. Connectez-vous au Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple montre comment utiliser sp_procoption pour définir l’exécution automatique d’une procédure.

EXEC sp_procoption @ProcName = N'<procedure name>'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';
GO

Arrêter l’exécution automatique d’une procédure

  1. Connectez-vous au Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple montre comment utiliser sp_procoption pour arrêter l’exécution automatique d’une procédure.

EXEC sp_procoption @ProcName = N'<procedure name>'      
    , @OptionName = 'startup'
    , @OptionValue = 'off';
GO

Étapes suivantes