Exécuter une procédure stockée

  • Article

S'applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 différentes manières 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. Une autre approche consiste à configurer la procédure stockée pour qu'elle s'exécute automatiquement au démarrage d'une instance de SQL Server.

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 constitue la première instruction du lot Transact-SQL.

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, ce code échoue s'il est exécuté dans le contexte d'une base de données dont le classement est sensible à la casse :

EXEC SP_heLP; -- Fails to resolve because SP_heLP doesn't 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

Utilisez les recommandations suivantes pour exécuter des procédures stockées.

Procédures stockées système

Les procédures système commencent par le préfixe sp_. Comme elles apparaissent logiquement dans toutes les bases de données définies par l'utilisateur et le système, les procédures système peuvent être exécutées à partir de n'importe quelle base de données sans qu'il soit nécessaire de qualifier complètement le nom de la procédure. Cependant, il est préférable de qualifier tous les noms de procédures du système avec le nom du schéma sys afin d'éviter les conflits de noms. L'exemple suivant illustre la méthode recommandée pour appeler une procédure système.

EXEC sys.sp_who;

Procédures stockées définies par l'utilisateur

Lors de l'exécution d'une procédure définie par l'utilisateur, il est préférable de qualifier le nom de la procédure avec le nom du schéma. Cette pratique permet d'améliorer légèrement les performances, car le moteur de base de données n'a pas à effectuer de recherches dans plusieurs schémas. L'utilisation du nom du schéma permet également d'éviter d'exécuter la mauvaise procédure si une base de données comporte des procédures portant le même nom dans plusieurs schémas.

Les exemples suivants illustrent la méthode recommandée pour exécuter une procédure définie par l'utilisateur. Cette procédure accepte deux paramètres d'entrée. Pour plus d'informations sur la spécification des paramètres d'entrée et de sortie, consultez Spécifier des paramètres dans une procédure stockée.

EXECUTE SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';
GO

Ou :

EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO

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

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

  2. Le schéma par défaut de l'appelant si la procédure s'exécute en batch ou en SQL dynamique. Si le nom de la procédure non qualifiée apparaît dans le corps d'une autre définition de procédure, le schéma qui contient cette autre procédure est recherché ensuite.

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

Sécurité

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

Autorisations

Pour plus d'informations sur les autorisations, reportez-vous à la section Autorisations dans EXECUTE (Transact-SQL).

Exécution d'une procédure stockée

Vous pouvez utiliser l'interface utilisateur SQL Server Management Studio (SSMS) ou Transact-SQL dans une fenêtre Requête SSMS pour exécuter une procédure stockée. Utilisez toujours la dernière version de SSMS.

Utiliser SQL Server Management Studio

  1. Dans l'Explorateur d'objets, connectez-vous à une instance de SQL Server ou de base de données Azure SQL, 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 que vous souhaitez exécuter, puis sélectionnez Exécuter la procédure stockée.

  4. Dans la zone de dialogue Exécuter la procédure, Le Paramètre indique le nom de chaque paramètre, le Type de données indique son type de données et le Paramètre de sortie indique s'il s'agit d'un paramètre de sortie.

    Pour chaque paramètre :

    • Sous Valeur, saisissez la valeur à utiliser pour le paramètre.
    • Sous Transférer une valeur nulle, choisissez de passer ou non un NULL comme valeur du paramètre.

  5. Pour exécuter la procédure stockée, sélectionnez OK. Si la procédure stockée n'a aucun paramètre, sélectionnez OK.

    La procédure stockée s'exécute et les résultats s'affichent dans le volet de résultats.

    Par exemple, pour exécuter la procédure stockée SalesLT.uspGetCustomerCompany à partir de l'article Créer une procédure stockée, saisissez Cannon pour le paramètre @LastName et Chris pour le paramètre @FirstName, puis sélectionnez OK. La procédure retourne FirstNameChris, LastNameCannon et CompanyNameÉquipement sportif extérieur.

Utilisez Transact-SQL dans une fenêtre Requête

  1. Dans SSMS, connectez-vous à une instance de SQL Server ou de base de données Azure SQL.

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

  3. Saisissez une instruction EXECUTE avec la syntaxe suivante dans la fenêtre Requête, en fournissant des valeurs pour tous les paramètres attendus :

    EXECUTE <ProcedureName> N'<Parameter 1 value>, N'<Parameter x value>;  
GO

    Par exemple, l'instruction Transact-SQL suivante exécute la procédure stockée uspGetCustomerCompany et avec Cannon comme valeur de paramètre @LastName et Chris comme valeur de paramètre @FirstName :

    EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO

  4. Dans la barre d'outils, sélectionnez Exécuter. La procédure stockée s'exécute.

Options pour les valeurs de paramètre

Il existe plusieurs façons de fournir des paramètres et des valeurs dans des instructions EXECUTE de procédure stockée. Les exemples suivants montrent plusieurs options différentes pour l'instruction EXECUTE.

  • Si vous fournissez les valeurs de paramètre dans le même ordre qu'elles sont définies dans la procédure stockée, vous n'avez pas besoin d'indiquer les noms de paramètres. Par exemple :

    EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';

  • Si vous fournissez des noms de paramètres dans le modèle @parameter_name=value, vous n'avez pas besoin de spécifier les noms et valeurs des paramètres dans le même ordre que celui dans lequel ils sont définis. Par exemple, l'une ou l'autre des déclarations suivantes est valable :

    EXEC SalesLT.uspGetCustomerCompany @FirstName = N'Chris', @LastName = N'Cannon';

    ou :

    EXEC SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';

  • Si vous utilisez le formulaire @parameter_name=value pour n'importe quel paramètre, vous devez l'utiliser pour tous les paramètres suivants dans cette instruction. Par exemple, vous ne pouvez pas utiliser EXEC SalesLT.uspGetCustomerCompany1 @FirstName = N'Chris', N'Cannon';.

Exécution automatique au démarrage

S'applique à : SQL Server

Dans SQL Server, un membre du rôle serveur sysadmin peut utiliser sp_procoption pour définir ou effacer une procédure pour l'exécution automatique au démarrage. Les procédures de démarrage doivent se trouver dans la base de données master, appartenir à sa, et ne peuvent pas avoir de paramètres d'entrée ou de sortie. Pour plus d'informations, consultez sp_procoption (Transact-SQL).

Les procédures marquées pour une exécution automatique au démarrage s'exécutent à chaque fois que SQL Server démarre et que la base de données master est récupérée au cours de ce 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. L'exécution automatique garantit qu'une telle table temporaire existe toujours lorsque tempdb est recréée au démarrage de SQL Server.

Une procédure exécutée automatiquement fonctionne avec les mêmes autorisations que les membres du rôle serveur fixe sysadmin. Les messages d'erreur générés par la procédure s'inscrivent dans le journal des erreurs de SQL Server.

Il n'y a pas de limite au nombre de procédures de démarrage que vous pouvez avoir, mais chaque procédure de démarrage consomme un thread de travail pendant son exécution. Si vous devez exécuter plusieurs procédures au démarrage, mais que vous n'avez pas besoin de les exécuter en parallèle, faites d'une procédure la procédure de démarrage et demandez à cette procédure d'appeler les autres procédures. Cette méthode n'utilise qu'un seul thread de travail.

Conseil

Ne renvoyez pas de jeux de résultats à partir d'une procédure exécutée automatiquement. Comme la procédure est exécutée par SQL Server et non par une application ou un utilisateur, les jeux de résultats ne peuvent aller nulle part.

Remarque

La base de données Azure SQL est conçu pour isoler les fonctionnalités des dépendances de la base de données master. Ainsi, les instructions Transact-SQL qui configurent les options au niveau du serveur ne sont pas disponibles dans Azure SQL. Vous pouvez souvent trouver des alternatives appropriées à partir d'autres services Azure tels que les travaux élastiques (aperçu) ou Azure Automation.

Définissez une procédure à exécuter automatiquement au start-up

Seul l'administrateur système (sa) peut marquer une procédure de sorte qu'elle s'exécute automatiquement.

  1. Dans SSMS, connectez-vous au moteur de base de données.

  2. Dans la barre d'outils Standard, sélectionnez Nouvelle Requête.

  3. Saisissez les commandes sp_procoption suivantes pour définir l'exécution automatique d'une procédure stockée au démarrage de SQL Server.

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

  4. Dans la barre d'outils, sélectionnez Exécuter.

Empêchez l'exécution automatique d'une procédure au démarrage

Un sysadmin peut utiliser sp_procoption pour empêcher l'exécution automatique d'une procédure au démarrage de SQL Server.

  1. Dans SSMS, connectez-vous au moteur de base de données.

  2. Dans la barre d'outils Standard, sélectionnez Nouvelle requête.

  3. Saisissez les commandes suivantes dans la fenêtre Requête.

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

  4. Dans la barre d'outils, sélectionnez Exécuter.

Contenu connexe