EXECUTE (Transact-SQL)

  • Article

Exécute une chaîne de commande ou de caractères dans un lot Transact-SQL, ou l'un des modules suivants : procédure stockée système, procédure stockée définie par l'utilisateur, procédure stockée CLR, fonction scalaire définie par l'utilisateur ou procédure stockée étendue.

Remarque relative à la sécuritéRemarque relative à la sécurité

Avant d'appeler EXECUTE avec une chaîne de caractères, validez cette dernière. N'exécutez jamais une commande construite par une entrée utilisateur qui n'a pas été validée.

SQL Server étend l'instruction EXECUTE de façon à pouvoir l'utiliser pour envoyer des commandes directes à des serveurs liés. De plus, il est possible de définir explicitement le contexte dans lequel une chaîne de caractères ou une commande s'exécute.

Les métadonnées pour le jeu de résultats peuvent être définies à l'aide des options WITH RESULT SETS.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

Execute a stored procedure or function
[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH <execute_option> [ ,...n ] ]
    }
[;]

Execute a character string
{ EXEC | EXECUTE } 
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
    [ AS { LOGIN | USER } = ' name ' ]
[;]

Execute a pass-through command against a linked server
{ EXEC | EXECUTE }
    ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ]
        [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]
    ) 
    [ AS { LOGIN | USER } = ' name ' ]
    [ AT linked_server_name ]
[;]

<execute_option>::=
{
        RECOMPILE 
    | { RESULT SETS UNDEFINED } 
    | { RESULT SETS NONE } 
    | { RESULT SETS ( <result_sets_definition> [,...n ] ) }
} 

<result_sets_definition> ::= 
{
    (
         { column_name 
           data_type 
         [ COLLATE collation_name ] 
         [ NULL | NOT NULL ] }
         [,...n ]
    )
    | AS OBJECT 
        [ db_name . [ schema_name ] . | schema_name . ] 
        {table_name | view_name | table_valued_function_name }
    | AS TYPE [ schema_name.]table_type_name
    | AS FOR XML 
}

Arguments

  • @return\_status
    Variable facultative de type entier qui stocke l'état du résultat d'un module. Cette variable doit être déclarée dans le traitement, la procédure stockée ou la fonction avant d'être utilisée dans une instruction EXECUTE.

    Lorsqu'elle est utilisée pour appeler une fonction scalaire définie par l'utilisateur, la variable @return\_status peut être de n'importe quel type de données scalaires.

  • module_name
    Nom complet ou partiel de la procédure stockée ou de la fonction scalaire définie par l'utilisateur à appeler. Les noms des modules doivent respecter les conventions sur les identificateurs. Les noms des procédures stockées étendues distinguent toujours les majuscules et les minuscules, quel que soit le classement du serveur.

    Un module créé dans une autre base de données peut être exécuté si l'utilisateur est propriétaire du module ou possède l'autorisation nécessaire pour l'exécuter dans la base de données en question. Un module peut être exécuté sur un autre serveur SQL Server si l'utilisateur qui l'exécute a l'autorisation nécessaire pour utiliser ce serveur (accès à distance) et pour exécuter le module dans la base de données. Si le nom d'un serveur est spécifié mais qu'aucun nom de base de données n'est spécifié, le Moteur de base de données SQL Server recherche le module dans la base de données par défaut de l'utilisateur.

  • ;number
    Entier facultatif qui regroupe les procédures de même nom. Ce paramètre n'est pas utilisé pour les procédures stockées étendues.

    [!REMARQUE]

    Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

    Pour plus d'informations sur les groupes de procédures, consultez CREATE PROCEDURE (Transact-SQL).

  • @module\_name\_var
    Nom d'une variable définie localement qui représente le nom d'un module.

  • @parameter
    Paramètre de module_name, tel qu'il est défini dans le module. Les noms des paramètres doivent être précédés du symbole arobase @). Lorsque les constantes et les noms de paramètres sont utilisés sous la forme @parameter\_name=value, il n'est pas nécessaire de les fournir dans l'ordre dans lequel ils sont définis dans le module. Cependant, si la forme @parameter\_name=value est utilisée pour un paramètre, elle doit l'être pour tous les paramètres suivants.

    Par défaut, les paramètres acceptent les valeurs NULL.

  • value
    Valeur du paramètre à passer au module ou à la commande directe. Si les noms des paramètres ne sont pas spécifiés, les valeurs des paramètres doivent être fournies dans l'ordre défini dans le module.

    Lors de l'exécution de commandes directes sur des serveurs liés, l'ordre des valeurs des paramètres dépend du fournisseur OLE DB du serveur lié. La plupart des fournisseurs OLE DB lient les valeurs aux paramètres de gauche à droite.

    Si la valeur d'un paramètre est le nom d'un objet, une chaîne de caractères, ou si elle est déterminée par le nom d'une base de données ou d'un schéma, le nom complet doit être placé entre guillemets simples. Si la valeur d'un paramètre est un mot clé, ce mot clé doit être mis entre guillemets doubles.

    Si une valeur par défaut est définie dans le module, l'utilisateur peut exécuter le module sans spécifier de paramètre.

    La valeur par défaut peut également être NULL. La définition du module indique généralement l'action à réaliser si la valeur d'un paramètre est NULL.

  • @variable
    Variable qui stocke un paramètre ou un paramètre de retour.

  • OUTPUT
    Spécifie que le module ou la chaîne de commandes renvoie un paramètre. Le paramètre correspondant dans le module ou la chaîne de commandes doit également avoir été créé à l'aide du mot clé OUTPUT. Utilisez ce mot clé lorsque vous utilisez des variables de curseur comme paramètres.

    Si l'argument value est défini en sortie (OUTPUT) d'un module exécuté sur un serveur lié, toute modification du paramètre @parameter correspondant effectuée par le fournisseur OLE DB est recopiée dans la variable à la fin de l'exécution du module.

    Si des paramètres OUTPUT sont utilisés et si vous souhaitez utiliser les valeurs de retour dans d'autres instructions à l'intérieur du module ou du lot d'appel, la valeur du paramètre doit être passée comme une variable (@parameter = @variable). Vous ne pouvez pas exécuter un module en spécifiant OUTPUT pour un paramètre qui n'est pas défini comme paramètre OUTPUT dans le module. Il est impossible de passer des constantes à un module en utilisant OUTPUT ; le paramètre de retour nécessite le nom d'une variable. Le type de données de la variable doit être déclaré et une valeur doit être affectée avant d'exécuter la procédure.

    Lorsque l'instruction EXECUTE est utilisée sur une procédure stockée distante ou pour exécuter une commande directe sur un serveur lié, les paramètres OUTPUT ne peuvent pas avoir l'un des types de données des objets LOB.

    Les paramètres de retour peuvent être de n'importe quel type, à l'exception des types LOB.

  • DEFAULT
    Fournit la valeur par défaut du paramètre telle qu'elle est définie dans le module. Une erreur se produit quand le module attend une valeur pour un paramètre qui n'a pas de valeur définie par défaut et qu'un paramètre est manquant ou que le mot clé DEFAULT est spécifié.

  • @string\_variable
    Nom d'une variable locale. @string\_variable peut être du type de données char, varchar, nchar ou nvarchar. Y compris les types (max).

  • [N] 'tsql_string'
    Chaîne constante. tsql_string peut être de n'importe quel type nvarchar ou varchar. Si N figure dans la chaîne, celle-ci est interprétée comme une donnée de type nvarchar.

  • AS <context_specification>
    Spécifie le contexte dans lequel l'instruction est exécutée.

  • LOGIN
    Spécifie que le contexte dont l'identité doit être empruntée est une connexion. L'étendue de l'emprunt d'identité est le serveur.

  • USER
    Spécifie que le contexte dont l'identité doit être empruntée est un utilisateur de la base de données active. L'étendue de l'emprunt d'identité est limitée à la base de données active. Le changement de contexte vers un utilisateur de base de données n'hérite pas des autorisations de cet utilisateur au niveau serveur.

    Important

    Lorsque le changement de contexte vers l'utilisateur de base de données est actif, toute tentative d'accès aux ressources en dehors de la base de données entraîne l'échec de l'instruction. Cela comprend les instructions USE database, les requêtes distribuées et les requêtes qui référencent une autre base de données au moyen d'identificateurs en trois ou quatre parties.

  • 'name'
    Nom d'utilisateur ou de connexion valide. name doit être membre du rôle serveur fixe sysadmin ou exister en tant que principal respectivement dans sys.database_principals ou sys.server_principals.

    name ne peut pas être un compte intégré tel que NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService ou NT AUTHORITY\LocalSystem.

    Pour plus d'informations, consultez Spécification d'un nom d'utilisateur ou de connexion, plus loin dans cette rubrique.

  • [N] 'command_string'
    Chaîne constante qui contient la commande à passer via le serveur lié. Si N figure dans la chaîne, celle-ci est interprétée comme une donnée de type nvarchar.

  • [?]
    Indique des paramètres dont les valeurs sont fournies dans la liste <arg-list> de commandes directes qui sont utilisées dans une instruction EXEC('…', <arg-list>) AT <linkedsrv>.

  • AT linked_server_name
    Spécifie que command_string est exécutée sur linked_server_name ; les éventuels résultats sont renvoyés au client. linked_server_name doit faire référence à une définition existante d'un serveur lié sur le serveur local. Les serveurs liés sont définis au moyen de sp_addlinkedserver.

  • WITH <execute_option>
    Options d'exécution possibles. Les options RESULT SETS ne peuvent pas être spécifiées dans une instruction INSERT… EXEC.

    Terme

    Définition

    RECOMPILE

    Impose la compilation, l'utilisation et la suppression d'un nouveau plan après l'exécution du module. S'il existe un plan de requêtes pour le module, ce plan reste en mémoire cache.

    Utilisez cette option si le paramètre que vous fournissez est atypique ou si les données ont changé de manière significative. Cette option n'est pas utilisée pour les procédures stockées étendues. Nous recommandons d'utiliser cette option modérément car elle est coûteuse.

    [!REMARQUE]

    Vous ne pouvez pas utiliser l'option WITH RECOMPILE lors de l'appel d'une procédure stockée qui utilise la syntaxe OPENDATASOURCE. L'option WITH RECOMPILE est ignorée lorsqu'un nom d'objet en quatre parties est spécifié.

    RESULT SETS UNDEFINED

    Cette option ne fournit aucune garantie quant aux résultats retournés, le cas échéant, et aucune définition n'est fournie. L'instruction s'exécute sans erreur si des résultats sont retournés ou aucun résultat n'est retourné. RESULT SETS UNDEFINED correspond au comportement par défaut si aucun result_sets_option n'est fourni.

    RESULT SETS NONE

    Garantit que l'instruction d'exécution ne retournera pas de résultats. Si des résultats sont retournés, le traitement est abandonné.

    <result_sets_definition>

    Offre une garantie selon laquelle le résultat reviendra comme spécifié dans result_sets_definition. Pour les instructions qui retournent plusieurs jeux de résultats, fournissez plusieurs sections result_sets_definition. Placez chaque result_sets_definition entre parenthèses, séparées par des virgules. Pour plus d'informations, consultez <result_sets_definition> plus loin dans cette rubrique.

  • <result_sets_definition>
    Décrit les jeux de résultats retournés par les instructions exécutées. Les clauses de result_sets_definition ont la signification suivante

    Terme

    Définition

    {

        column_name

        data_type

        [ COLLATE collation_name]

        [NULL | NOT NULL]

    }

    Terme

    Définition

    column_name

    Nom de chaque colonne. Si le nombre de colonnes diffère du jeu de résultats, une erreur se produit et le lot est abandonné. Si le nom d'une colonne diffère du jeu de résultats, le nom de colonne retourné correspondra au nom défini.

    data_type

    Types de données de chaque colonne. Si les types de données diffèrent, une conversion implicite vers le type de données défini est effectuée. Si la conversion échoue, le lot est abandonné

    COLLATE collation_name

    Classement de chaque colonne. En cas d'incompatibilité de classement, un classement implicite est tenté. Si cette opération échoue, le lot est abandonné.

    NULL | NOT NULL

    Valeur Null possible dans chaque colonne. Si la possibilité de valeur NULL définie est NOT NULL et que les données retournées contiennent des valeurs NULL, une erreur a lieu et le lot est abandonné. En l'absence de spécification, la valeur par défaut se conforme au paramètre des options ANSI_NULL_DFLT_ON et ANSI_NULL_DFLT_OFF.

    db_name

    Nom de la base de données contenant la table, la vue ou la fonction table.

    schema_name

    Nom du schéma propriétaire de la table, de la vue ou de la fonction table.

    table_name | view_name | table_valued_function_name

    Spécifie que les colonnes retournées seront celles spécifiées dans la table, la vue ou la fonction table nommée. Les variables de table, les tables temporaires et les synonymes ne sont pas pris en charge dans la syntaxe d'objet d'AS.

    AS TYPE [schema_name.]table_type_name

    Spécifie que les colonnes retournées seront celles spécifiées dans le type de table.

    AS FOR XML

    Spécifie que les résultats XML de l'instruction, ou de la procédure stockée appelée par l'instruction EXECUTE, sont convertis dans le même format que s'ils avaient été produits par une instruction SELECT … FOR XML …. Tous les formats des directives de type dans l'instruction d'origine sont supprimés, et les résultats retournés sont les mêmes que si aucune directive de type n'avait été spécifiée. AS FOR XML ne convertit pas les résultats tabulaires non XML de l'instruction ou de la procédure stockée exécutée en XML.

    Le jeu de résultats réel qui est retourné pendant l'exécution peut différer du résultat défini à l'aide de la clause WITH RESULT SETS de l'une des manières suivantes : nombre de jeux de résultats, nombre de colonnes, nom de colonne, possibilité de valeur NULL et type de données. Si le nombre de jeu de résultats diffère, une erreur se produit et le lot est abandonné.

Notes

Il est possible de fournir les paramètres en utilisant value ou @parameter\_name = value. Un paramètre ne fait pas partie d'une transaction ; par conséquent, si vous modifiez l'un d'eux dans une transaction et que vous restaurez cette dernière par la suite, le paramètre ne reprend pas sa valeur initiale. La valeur renvoyée à l'appelant est toujours la valeur au moment du renvoie du module.

L'imbrication a lieu lorsqu'un module en appelle un autre ou exécute du code managé en faisant référence à un module CLR (Common Language Runtime), un type défini par l'utilisateur ou un agrégat. Le niveau d'imbrication augmente au début de l'exécution du module appelé ou de la référence au code managé ; il diminue à la fin de l'exécution du module appelé ou de la référence au code managé. Au-delà de 32 niveaux d'imbrication, l'ensemble de la chaîne d'appel échoue. Le niveau d'imbrication actuel est stocké dans la fonction système @@NESTLEVEL.

Les procédures stockées distantes et les procédures stockées étendues n'étant pas incluses dans l'étendue d'une transaction (à moins qu'elles proviennent d'une instruction BEGIN DISTRIBUTED TRANSACTION ou qu'elles soient utilisées avec des options de configuration diverses), il est impossible d'annuler les commandes exécutées par l'appel de ces procédures. Pour plus d'informations, consultez Procédures stockées système (Transact-SQL) et BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Lorsque vous utilisez des variables de curseur, une erreur se produit si vous exécutez une procédure qui passe une variable de curseur avec un curseur qui lui est alloué.

Il n'est pas nécessaire de spécifier le mot clé EXECUTE lorsque vous exécutez des modules si l'instruction est la première d'un lot.

Pour plus d'informations spécifiques aux procédures stockées CLR, consultez Procédures stockées CLR.

Utilisation de l'instruction EXECUTE avec des procédures stockées

Il n'est pas nécessaire de spécifier le mot clé EXECUTE lorsque vous exécutez des procédures stockées si l'instruction est la première du traitement.

Les procédures stockées système SQL Server commencent par les caractères sp_. Elles sont stockées physiquement dans la base de données des ressources, mais apparaissent logiquement dans le schéma sys de chaque base de données système et de chaque base de données définie par l'utilisateur. Lorsque vous exécutez une procédure stockée système, que ce soit dans un lot ou dans un module, telle qu'une procédure stockée ou une fonction définie par l'utilisateur, il est recommandé de qualifier son nom avec le nom de schéma sys.

Les procédures stockées étendues système SQL Server commencent par les caractères xp_, ; elles se trouvent dans le schéma dbo de la base de données master. Lorsque vous exécutez une procédure stockée système étendue, que ce soit dans un lot ou dans un module, telle qu'une procédure stockée ou une fonction définie par l'utilisateur, il est recommandé de qualifier son nom avec master.dbo.

Lorsque vous exécutez une procédure stockée définie par l'utilisateur, que ce soit dans un lot ou dans un module, telle qu'une procédure stockée ou une fonction définie par l'utilisateur, il est recommandé de qualifier son nom avec un nom de schéma. Il n'est pas conseillé de nommer une procédure stockée définie par l'utilisateur avec le même nom qu'une procédure stockée système. Pour plus d'informations sur l'exécution des procédures stockées, consultez Exécuter une procédure stockée.

Utilisation de l'instruction EXECUTE avec une chaîne de caractères

Dans les versions antérieures de SQL Server, les chaînes de caractères sont limitées à 8 000 octets. Cela nécessite de concaténer les chaînes volumineuses pour l'exécution dynamique. Dans SQL Server, il est possible de spécifier les types de données varchar(max) et nvarchar(max) qui permettent aux chaînes de caractères d'occuper jusqu'à 2 gigaoctets (Go).

Les modifications du contexte de la base de données ne durent que jusqu'à la fin de l'instruction EXECUTE. Dans l'exemple qui suit, après l'exécution de l'instruction EXEC, le contexte de la base de données est master.

USE master; EXEC ('USE AdventureWorks2012; SELECT BusinessEntityID, JobTitle FROM HumanResources.Employee;');

Changement de contexte

Vous pouvez utiliser la clause AS { LOGIN | USER } = ' name ' pour changer le contexte d'exécution d'une instruction dynamique. Lorsque le changement de contexte est spécifié sous la forme EXECUTE ('string') AS <context_specification>, la durée du changement est limitée à l'étendue de la requête en cours d'exécution.

Spécification d'un utilisateur ou d'un nom d'accès

L'utilisateur ou le nom de connexion spécifié dans AS { LOGIN | USER } = ' name ' doit exister comme principal dans respectivement sys.database_principals ou sys.server_principals, faute de quoi l'instruction échouera. De plus, les autorisations IMPERSONATE doivent être accordées sur le principal. À moins que l'appelant soit le propriétaire de la base de données ou un membre du rôle serveur fixe sysadmin, le principal doit exister même lorsque l'utilisateur accède à la base de données ou à une instance de SQL Server par le biais d'une appartenance à un groupe Windows. Par exemple, supposons les conditions suivantes :

  • Le groupe CompanyDomain\SQLUsers a accès à la base de données Sales.

  • CompanyDomain\SqlUser1 est membre de SQLUsers et de ce fait bénéficie d'un accès implicite à la base de données Sales.

Bien que CompanyDomain\SqlUser1 ait accès à la base de données grâce à son appartenance au groupe SQLUsers, l'instruction EXECUTE @string\_variable AS USER = 'CompanyDomain\SqlUser1' échoue du fait que CompanyDomain\SqlUser1 n'existe pas en tant que principal dans la base de données.

Recommandations

Spécifiez une connexion ou un utilisateur qui possède les privilèges minimum requis pour effectuer les opérations définies dans l'instruction ou le module. Par exemple, ne spécifiez pas un nom de connexion qui a des autorisations au niveau serveur, si seules des autorisations au niveau base de données sont requises. Ne spécifiez pas non plus le compte d'un propriétaire de base de données, excepté si ces autorisations sont exigées.

Autorisations

Aucune autorisation n'est requise pour exécuter l'instruction EXECUTE. Cependant, des autorisations sont requises sur les éléments sécurisables référencés dans la chaîne EXECUTE. Par exemple, si la chaîne contient une instruction INSERT, l'appelant de l'instruction EXECUTE doit posséder l'autorisation INSERT sur la table cible. Les autorisations sont vérifiées au moment où l'instruction EXECUTE est rencontrée, même si celle-ci est incluse dans un module.

Les autorisations EXECUTE pour un module sont accordées par défaut au propriétaire du module, qui peut les transmettre à d'autres utilisateurs. Lorsqu'un module qui exécute une chaîne est lancé, les autorisations sont vérifiées dans le contexte non pas de l'utilisateur qui a créé le module, mais de celui qui exécute le module. Cependant, si le même utilisateur est propriétaire du module appelant et du module appelé, la vérification de l'autorisation EXECUTE n'a pas lieu pour le second module.

Si le module accède à d'autres objets de la base de données, l'exécution réussit lorsque vous avez l'autorisation EXECUTE sur le module et si l'une des conditions suivantes est remplie :

  • Le module est repéré avec EXECUTE AS USER ou SELF et son propriétaire a les autorisations correspondantes sur l'objet référencé. Pour plus d'informations sur l'emprunt d'identité dans un module, consultez Clause EXECUTE AS (Transact-SQL).

  • Le module est repéré avec EXECUTE AS CALLER et vous avez les autorisations correspondantes sur l'objet.

  • Le module est repéré avec EXECUTE AS EXECUTE AS user_name et user_name a les autorisations correspondantes sur l'objet.

Autorisations pour les changements de contexte

Pour spécifier EXECUTE AS sur une connexion, l'appelant doit posséder les autorisations IMPERSONATE sur le nom de connexion spécifié. Pour spécifier EXECUTE AS sur un utilisateur de base de données, l'appelant doit posséder les autorisations IMPERSONATE sur le nom d'utilisateur spécifié. Lorsqu'aucun contexte d'exécution n'est spécifié ou lorsque EXECUTE AS CALLER est spécifié, les autorisations IMPERSONATE ne sont pas requises.

Exemples

A.Utilisation de l'instruction EXECUTE pour passer un seul paramètre

La procédure stockée uspGetEmployeeManagers attend un seul paramètre (@EmployeeID). Dans les codes exemple suivants, la procédure stockée uspGetEmployeeManagers est exécutée avec Employee ID 6 comme valeur de paramètre.

USE AdventureWorks2012;
GO
EXEC dbo.uspGetEmployeeManagers 6;
GO

La variable peut être désignée explicitement dans l'exécution :

EXEC dbo.uspGetEmployeeManagers @EmployeeID = 6;
GO

Si l'instruction suivante est la première d'un traitement ou d'un script osql ou sqlcmd, EXEC n'est pas nécessaire :

dbo.uspGetEmployeeManagers 6;
GO
--Or
dbo.uspGetEmployeeManagers @EmployeeID = 6;
GO

B.Utilisation de plusieurs paramètres

Le code exemple suivant exécute la procédure stockée spGetWhereUsedProductID. Il passe deux paramètres : le premier est l'identificateur d'un produit (819), et le second paramètre, @CheckDate,, est une valeur datetime.

USE AdventureWorks2012;
GO
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

C.Utilisation de l'instruction EXECUTE 'tsql_string' avec une variable

Le code exemple suivant montre comment EXECUTE manipule les chaînes construites dynamiquement qui contiennent des variables. Cet exemple crée le curseur tables_cursor pour contenir la liste de toutes les tables définies par l'utilisateur dans la base de données AdventureWorks2012 , puis utilise cette liste pour reconstruire tous les index des tables.

USE AdventureWorks2012;
GO
DECLARE tables_cursor CURSOR
   FOR
   SELECT s.name, t.name 
   FROM sys.objects AS t
   JOIN sys.schemas AS s ON s.schema_id = t.schema_id
   WHERE t.type = 'U';
OPEN tables_cursor;
DECLARE @schemaname sysname;
DECLARE @tablename sysname;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
   EXECUTE ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;');
   FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
END;
PRINT 'The indexes on all tables have been rebuilt.';
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO

D.Utilisation de l'instruction EXECUTE avec une procédure stockée distante

Le code exemple suivant exécute la procédure stockée uspGetEmployeeManagers sur le serveur distant SQLSERVER1 et stocke dans @retstat l'état du résultat qui indique la réussite ou l'échec.

DECLARE @retstat int;
EXECUTE @retstat = SQLSERVER1.AdventureWorks2012.dbo.uspGetEmployeeManagers @BusinessEntityID = 6;

E.Utilisation de l'instruction EXECUTE avec une variable de procédure stockée

Le code exemple suivant crée une variable qui représente le nom d'une procédure stockée.

DECLARE @proc_name varchar(30);
SET @proc_name = 'sys.sp_who';
EXEC @proc_name;

F.Utilisation de l'instruction EXECUTE avec l'option DEFAULT

Le code exemple suivant crée une procédure stockée avec des valeurs par défaut pour les premier et troisième paramètres. Lorsque la procédure est exécutée, ces valeurs par défaut sont insérées pour les premier et troisième paramètres si aucune valeur n'est passée dans l'appel ou si la valeur par défaut est spécifiée. Notez les différentes façons d'utiliser le mot clé DEFAULT.

USE AdventureWorks2012;
GO
IF OBJECT_ID(N'dbo.ProcTestDefaults', N'P')IS NOT NULL
   DROP PROCEDURE dbo.ProcTestDefaults;
GO
-- Create the stored procedure.
CREATE PROCEDURE dbo.ProcTestDefaults (
@p1 smallint = 42, 
@p2 char(1), 
@p3 varchar(8) = 'CAR')
AS 
   SET NOCOUNT ON;
   SELECT @p1, @p2, @p3
;
GO

L'exécution de la procédure stockée Proc_Test_Defaults peut s'effectuer selon plusieurs combinaisons.

-- Specifying a value only for one parameter (@p2).
EXECUTE dbo.ProcTestDefaults @p2 = 'A';
-- Specifying a value for the first two parameters.
EXECUTE dbo.ProcTestDefaults 68, 'B';
-- Specifying a value for all three parameters.
EXECUTE dbo.ProcTestDefaults 68, 'C', 'House';
-- Using the DEFAULT keyword for the first parameter.
EXECUTE dbo.ProcTestDefaults @p1 = DEFAULT, @p2 = 'D';
-- Specifying the parameters in an order different from the order defined in the procedure.
EXECUTE dbo.ProcTestDefaults DEFAULT, @p3 = 'Local', @p2 = 'E';
-- Using the DEFAULT keyword for the first and third parameters.
EXECUTE dbo.ProcTestDefaults DEFAULT, 'H', DEFAULT;
EXECUTE dbo.ProcTestDefaults DEFAULT, 'I', @p3 = DEFAULT;

G.Utilisation de l'instruction EXECUTE avec AT linked_server_name

Le code exemple suivant passe une chaîne de commandes à un serveur distant. Il crée un serveur lié SeattleSales qui pointe vers une autre instance de SQL Server et exécute une instruction DDL (CREATE TABLE) sur ce serveur lié.

EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
EXECUTE ( 'CREATE TABLE AdventureWorks2012.dbo.SalesTbl 
(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;
GO

H.Utilisation de l'instruction EXECUTE WITH RECOMPILE

Le code exemple suivant exécute la procédure stockée Proc_Test_Defaults et impose la compilation, l'utilisation et la suppression d'un nouveau plan de requête après l'exécution du module.

EXECUTE dbo.Proc_Test_Defaults @p2 = 'A' WITH RECOMPILE;
GO

I.Utilisation de l'instruction EXECUTE avec une fonction définie par l'utilisateur

Le code exemple suivant exécute la fonction scalaire ufnGetSalesOrderStatusText définie par l'utilisateur. Il utilise la variable @returnstatus pour stocker la valeur renvoyée par la fonction. Celle-ci attend un seul paramètre d'entrée, @Status, défini avec le type de donnée tinyint.

USE AdventureWorks2012;
GO
DECLARE @returnstatus nvarchar(15);
SET @returnstatus = NULL;
EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2;
PRINT @returnstatus;
GO

J.Utilisation de l'instruction EXECUTE pour interroger une base de données Oracle sur un serveur lié

Le code exemple suivant exécute plusieurs instructions SELECT sur le serveur Oracle distant. Il commence par ajouter le serveur Oracle comme serveur lié puis crée ensuite la connexion au serveur lié.

-- Setup the linked server.
EXEC sp_addlinkedserver  
        @server='ORACLE',
        @srvproduct='Oracle',
        @provider='OraOLEDB.Oracle', 
        @datasrc='ORACLE10';

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname='ORACLE',
    @useself='false', 
    @locallogin=null, 
    @rmtuser='scott', 
    @rmtpassword='tiger';
 
EXEC sp_serveroption 'ORACLE', 'rpc out', true;
GO
 
-- Execute several statements on the linked Oracle server.
EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', 7902) AT ORACLE;
GO
DECLARE @v INT; 
SET @v = 7902;
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', @v) AT ORACLE;
GO

K.Utilisation de l'instruction EXECUTE AS USER pour basculer le contexte vers un autre utilisateur

Le code exemple suivant exécute une chaîne Transact-SQL qui crée une table et spécifie la clause AS USER pour basculer le contexte d'exécution de l'instruction de l'appelant vers l'utilisateur User1. Le Moteur de base de données vérifie les autorisations de User1 lorsque l'instruction est exécutée. User1 doit exister dans la base de données et posséder l'autorisation de créer des tables dans le schéma Sales, faute de quoi l'instruction échoue.

USE AdventureWorks2012;
GO
EXECUTE ('CREATE TABLE Sales.SalesTable (SalesID int, SalesName varchar(10));')
AS USER = 'User1';
GO

L.Utilisation d'un paramètre avec l'instruction EXECUTE et AT linked_server_name

L'exemple suivant passe une chaîne de commande à un serveur distant en utilisant un point d'interrogation (?) comme espace réservé pour un paramètre. Cet exemple crée un serveur lié SeattleSales qui pointe vers une autre instance de SQL Server et exécute une instruction SELECT sur ce serveur lié. L'instruction SELECT utilise le point d'interrogation comme espace réservé pour le paramètre ProductID (952) qui est fourni après l'instruction.

-- Setup the linked server.
EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'
GO
-- Execute the SELECT statement.
EXECUTE ('SELECT ProductID, Name 
    FROM AdventureWorks2012.Production.Product
    WHERE ProductID = ? ', 952) AT SeattleSales;
GO

M.Utilisation de l'instruction EXECUTE pour redéfinir un jeu de résultats unique

Certains des exemples précédents ont exécuté EXEC dbo.uspGetEmployeeManagers 6; qui a retourné 7 colonnes. L'exemple suivant montre l'utilisation de la syntaxe WITH RESULT SET pour modifier les noms et types de données du jeu de résultats retourné.

USE AdventureWorks2012;
GO

EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
( 
   ([Reporting Level] int NOT NULL,
    [ID of Employee] int NOT NULL,
    [Employee First Name] nvarchar(50) NOT NULL,
    [Employee Last Name] nvarchar(50) NOT NULL,
    [Employee ID of Manager] nvarchar(max) NOT NULL,
    [Manager First Name] nvarchar(50) NOT NULL,
    [Manager Last Name] nvarchar(50) NOT NULL )
);

N.Utilisation de l'instruction EXECUTE pour redéfinir deux jeux de résultats

Lorsque vous exécutez une instruction qui retourne plusieurs jeux de résultats, définissez chaque jeu de résultats attendu. L'exemple suivant dans AdventureWorks2012 crée une procédure qui retourne deux jeux de résultats. Ensuite, la procédure est exécutée à l'aide de la clause WITH RESULT SETS et la spécification de deux définitions de jeu de résultats.

USE AdventureWorks2012;
GO

--Create the procedure
CREATE PROC Production.ProductList @ProdName nvarchar(50)
AS
-- First result set
SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE Name LIKE @ProdName;
-- Second result set 
SELECT Name, COUNT(S.ProductID) AS NumberOfOrders
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS S
        ON P.ProductID  = S.ProductID 
    WHERE Name LIKE @ProdName
    GROUP BY Name;
GO

-- Execute the procedure 
EXEC Production.ProductList '%tire%'
WITH RESULT SETS 
(
    (ProductID int,   -- first result set definition starts here
    Name Name,
    ListPrice money)
    ,                 -- comma separates result set definitions
    (Name Name,       -- second result set definition starts here
    NumberOfOrders int)
);

Voir aussi

Référence

@@NESTLEVEL (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

Clause EXECUTE AS (Transact-SQL)

Utilitaire osql

REVERT (Transact-SQL)

sp_addlinkedserver (Transact-SQL)

Utilitaire sqlcmd

SUSER_NAME (Transact-SQL)

sys.database_principals (Transact-SQL)

sys.server_principals (Transact-SQL)

USER_NAME (Transact-SQL)

OPENDATASOURCE (Transact-SQL)

Concepts

Principaux (moteur de base de données)