Partager via

  • Article

CREATE PROCEDURE (Transact-SQL)

Crée une procédure stockée. Une procédure stockée est une collection enregistrée d'instructions Transact-SQL ou une référence à une méthode CLR (Common Language Runtime) de Microsoft .NET Framework qui peut accepter et retourner des paramètres fournis par l'utilisateur. Les procédures peuvent être créées pour une utilisation permanente ou temporaire au sein d'une même session (c'est-à-dire une procédure locale temporaire) ou pour une utilisation temporaire dans toutes les sessions (c'est-à-dire une procédure globale temporaire).

Vous pouvez également créer des procédures stockées à exécuter automatiquement au démarrage d'une instance de SQL Server.

Icône Lien de rubriqueConventions de syntaxe Transact-SQL

Syntaxe

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

Arguments

  • schema_name
    Nom du schéma auquel appartient la procédure.

  • procedure_name
    Nom de la nouvelle procédure stockée. Le nom des procédures doit respecter les règles applicables aux identificateurs et doit être unique dans tout le schéma.

    Nous vous recommandons vivement de ne pas utiliser le préfixe sp_ dans le nom de la procédure. En effet, ce préfixe est utilisé par SQL Server pour faire référence aux procédures stockées système. Pour plus d'informations, consultez Création de procédures stockées (moteur de base de données).

    Les procédures temporaires locales ou globales peuvent être créées en faisant précéder le nom de la procédure procedure_name, par un signe dièse unique (#procedure_name) pour les procédures temporaires locales, et par deux signes dièse (##procedure_name) pour les procédures temporaires globales. Des noms temporaires ne peuvent pas être indiqués pour les procédures stockées CLR.

    Le nom complet d'une procédure stockée ou d'une procédure stockée temporaire globale, y compris les signes ##, ne peut dépasser 128 caractères. Le nom complet d'une procédure stockée temporaire locale, y compris le signe #, ne peut dépasser 116 caractères.

  • **;**number
    Entier facultatif utilisé pour les procédures de groupes portant le même nom. Ces procédures groupées peuvent être supprimées en même temps par le biais d'une seule instruction DROP PROCEDURE. Par exemple, les procédures utilisées avec une application appelée orders peuvent être nommées orderproc;1, orderproc;2, etc. L'instruction DROP PROCEDURE orderproc supprime le groupe tout entier. Si le nom contient des identificateurs délimités, le numéro ne doit pas être compris dans l'identificateur, utilisez le délimiteur adéquat autour de procedure_name uniquement.

    Les procédures stockées numérotées font l'objet des restrictions suivantes :

    • Elles ne peuvent pas utiliser les types personnalisés de données xml ou CLR.

    • Elles ne peuvent pas créer de repère de plan sur une procédure stockée numérotée.

    [!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é.

  • **@**parameter
    Paramètre de la procédure. Vous pouvez déclarer un ou plusieurs paramètres dans une instruction CREATE PROCEDURE. La valeur de chaque paramètre déclaré doit être fournie par l'utilisateur lors de l'appel à la procédure, sauf si vous définissez une valeur par défaut pour le paramètre ou que sa valeur est définie sur un autre paramètre. Une procédure stockée peut comprendre au maximum 2 100 paramètres. Si une procédure contient des paramètres table et que le paramètre n'est pas indiqué dans l'appel, c'est une table vide qui est passée par défaut.

    Spécifiez un nom de paramètre en plaçant le signe @ comme premier caractère. Ce nom doit respecter les règles applicables aux identificateurs. Un paramètre étant local à une procédure, vous pouvez utiliser le même nom dans d'autres procédures. Par défaut, les paramètres ne peuvent que prendre la place d'expressions constantes ; ils ne peuvent pas être utilisés à la place de noms de tables, de colonnes ou d'autres objets base de données. Pour plus d'informations, consultez EXECUTE (Transact-SQL).

    Les paramètres ne peuvent pas être déclarés si FOR REPLICATION est précisé.

  • [ type_schema_name**.** ] data_type
    Type de données du paramètre et du schéma auquel le paramètre appartient. Tous les types de données peuvent être utilisés comme paramètre dans une procédure stockée Transact-SQL. Vous pouvez utiliser un type de table défini par l'utilisateur pour déclarer un paramètre table comme paramètre pour une procédure stockée Transact-SQL. Les paramètres table peuvent être spécifiés uniquement comme paramètres d'entrée et ils doivent être accompagnés d'un mot clé READONLY. Le type de données cursor ne peut être utilisé que dans des paramètres OUTPUT. Lorsque vous spécifiez un type de données cursor, vous devez également spécifier les mots clés VARYING et OUTPUT. Plusieurs paramètres de sortie portant le type de données cursor peuvent être spécifiés.

    Dans le cas de procédures stockées CLR, les types char, varchar, text, ntext, image, cursor, les types de tables définis par l'utilisateur et table ne peuvent servir de paramètre. Pour plus d'informations sur la correspondance entre les types de données CLR et les types SQL Server fournis par le système, consultez Mappage des données de paramètres CLR. Pour plus d'informations sur les types SQL Server de données système, consultez Types de données (Transact-SQL).

    Si le type du paramètre correspond à un type CLR défini par un utilisateur, vous devez dans ce cas bénéficier de l'autorisation EXECUTE sur ce type.

    Si type_schema_name n'est pas précisé, le moteur de base de données SQL Server pointe sur type_name dans l'ordre suivant :

    • types de données SQL Server fournis par le système ;

    • schéma par défaut de l'utilisateur actuel dans la base de données actuelle ;

    • schéma dbo dans la base de données actuelle.

    Dans le cas de procédures stockées numérotées, le type de données ne peut correspondre au type xml ou au type CLR défini par l'utilisateur.

  • VARYING
    Indique l'ensemble de résultats pris en charge sous forme de paramètre de sortie. Ce paramètre est construit dynamiquement par la procédure stockée ; il se peut donc que son contenu varie. S'applique uniquement aux paramètres de type cursor.

  • default
    Valeur par défaut pour le paramètre. Si vous avez défini une valeur par défaut indiquée par default, la procédure peut être exécutée sans spécifier de valeur pour ce paramètre. La valeur par défaut doit être une constante ou elle peut avoir la valeur NULL. Si la procédure utilise le paramètre associé au mot clé LIKE, elle peut contenir les caractères génériques suivants : % _ [] et [^].

    [!REMARQUE]

    Les valeurs par défaut sont reprises dans la colonne sys.parameters.default uniquement pour les procédures CLR. Cette colonne correspond à NULL pour les paramètres de procédures Transact-SQL.

  • OUTPUT
    Indique que le paramètre est un paramètre de sortie. La valeur de cette option peut être retournée à l'instruction EXECUTE appelante. Utilisez les paramètres OUTPUT pour retourner les valeurs à la procédure appelante. Les paramètres de type text, ntext et image ne peuvent servir de paramètres OUTPUT à moins que la procédure corresponde à une procédure CLR. Un paramètre de sortie qui utilise le mot clé OUTPUT peut être un espace réservé pour curseur, sauf si la procédure correspond à une procédure CLR (Common Language Runtime). Un type de table défini par l'utilisateur ne peut pas être spécifié comme paramètre OUTPUT d'une procédure stockée.

  • READONLY
    Indique que le paramètre ne peut pas être mis à jour ou modifié dans le corps de la procédure. Si le type de paramètre est un type de table défini par l'utilisateur, READONLY doit être spécifié.

  • RECOMPILE
    Indique que le moteur de base de données n'utilise pas le cache pour le plan de cette procédure et que la procédure est recompilée à l'exécution. Cette option ne peut pas être utilisée si FOR REPLICATION est indiqué. RECOMPILE ne peut pas être précisé pour une utilisation avec des procédures stockées CLR.

    Pour ordonner au moteur de base de données d'annuler les plans relatifs à des requêtes individuelles au sein d'une procédure stockée, utilisez dans ce cas l'indicateur de requête RECOMPILE. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL). N'utilisez cet indicateur que si des valeurs atypiques ou temporaires sont utilisées dans un sous-ensemble de requêtes appartenant à la procédure stockée.

  • ENCRYPTION
    Indique que SQL Server se charge de convertir le texte d'origine provenant de l'instruction CREATE PROCEDURE dans un format d'obfuscation. La sortie générée par l'obfuscation n'est pas visible directement dans n'importe quel affichage catalogue de SQL Server. Les utilisateurs n'ayant pas accès aux tables système ou aux fichiers de base de données ne peuvent pas récupérer le texte d'obfuscation. Le texte est cependant à la disposition des utilisateurs dotés de privilèges qui accèdent aux tables système via le port DAC ou qui accèdent directement aux fichiers de bases de données. Les utilisateurs qui peuvent attacher un débogueur au processus serveur peuvent également récupérer la procédure déchiffrée de la mémoire à l'exécution. Pour plus d'informations sur l'accès aux métadonnées système, consultez Configuration de la visibilité des métadonnées.

    Cette option n'est pas valide pour les procédures stockées CLR.

    Les procédures créées à l'aide de cette option ne peuvent pas être publiées par le biais d'une réplication SQL Server.

  • EXECUTE AS
    Indique le contexte de sécurité dans lequel la procédure stockée doit être exécutée.

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

  • FOR REPLICATION
    Indique que les procédures stockées créées pour la réplication ne peuvent pas être exécutées sur l'Abonné. Une procédure stockée créée avec l'option FOR REPLICATION est utilisée comme filtre de procédure stockée et n'est exécutée que pendant la réplication. Les paramètres ne peuvent pas être déclarés si FOR REPLICATION est précisé. FOR REPLICATION ne peut pas être précisé pour une utilisation avec des procédures stockées CLR. L'option RECOMPILE est ignorée lorsqu'une procédure est créée par le biais de FOR REPLICATION.

    Une procédure FOR REPLICATION possèdera une marque RF dans sys.objects et dans sys.procedures de type objet.

  • <sql_statement>
    Une ou plusieurs instructions Transact-SQL à passer à la procédure. Pour plus d'informations sur les restrictions qui s'y appliquent, consultez la section Notes .

  • EXTERNAL NAME assembly_name**.class_name.method_name
    Précise la méthode d'un assembly .NET Framework pour créer une référence à une procédure stockée CLR. class_name doit être un identificateur SQL Server valide et doit exister au sein d'une classe de l'assembly. Si la classe possède un nom qualifié par un espace de noms utilisant un point (    .) afin de séparer les parties constituant l'espace de noms, le nom de la classe doit alors être délimité par des crochets ([]) ou des guillemets droits (""**). La méthode spécifiée doit être une méthode statique de la classe.

    [!REMARQUE]

    Par défaut, SQL Server ne peut pas exécuter du code CLR. Vous pouvez créer, modifier et supprimer des objets de base de données faisant référence à des modules du CLR mais vous ne pouvez pas exécuter ces références dans SQL Server tant que l'option clr enabled n'est pas activée. sp_configure permet d'activer cette option.

Notes

Il n'existe pas de taille maximale prédéfinie pour une procédure stockée.

Une procédure stockée définie par l'utilisateur ne peut être créée que dans la base de données actuelle. Les procédures temporaires constituent cependant l'exception car elles sont toujours créées dans tempdb. Si un nom de schéma n'est pas précisé, le schéma par défaut de l'utilisateur chargé de créer la procédure est utilisé. Pour plus d'informations sur les schémas, consultez Séparation du schéma et de l'utilisateur.

L'instruction CREATE PROCEDURE ne peut pas s'utiliser conjointement avec d'autres instructions Transact-SQL dans un même traitement.

Par défaut, les paramètres peuvent accepter des valeurs NULL. Si une valeur de paramètre NULL est passée et que ce paramètre est utilisé dans une instruction CREATE TABLE ou ALTER TABLE dans laquelle la colonne référencée n'accepte pas les valeurs NULL, le moteur de base de données génère une erreur. Pour éviter ce cas de figure, ajoutez une logique programmée à la procédure ou utilisez une valeur par défaut pour la colonne (en utilisant le mot clé DEFAULT avec l'instruction CREATE TABLE ou ALTER TABLE).

Nous vous recommandons de préciser explicitement la valeur NULL ou NOT NULL pour chaque colonne d'une table temporaire. Les options ANSI_DFLT_ON et ANSI_DFLT_OFF définissent la manière dont le moteur de base de données assigne les attributs NULL ou NOT NULL aux colonnes, s'ils ne sont pas spécifiés dans une instruction CREATE TABLE ou ALTER TABLE. Si une connexion exécute une procédure stockée avec des valeurs différentes pour ces options que pour la connexion qui a créé la procédure, les colonnes de la table créée par la seconde connexion peuvent avoir des valeurs NULL différentes et présenter ainsi des comportements différents. Si NULL ou NOT NULL est explicitement établi pour chaque colonne, les tables temporaires sont créées avec la même possibilité de valeurs NULL pour toutes les connexions qui exécutent la procédure stockée.

Utilisation des options SET

Le moteur de base de données enregistre les paramètres de SET QUOTED_IDENTIFIER et de SET ANSI_NULLS lors de la création ou de la modification d'une procédure stockée Transact-SQL. Ces paramètres d'origine sont utilisés lors de l'exécution de la procédure stockée. Par conséquent, tous les paramètres de la session cliente pour SET QUOTED_IDENTIFIER et SET ANSI_NULLS sont ignorés lors de l'exécution de la procédure stockée. D'autres options SET, telles que SET ARITHABORT, SET ANSI_WARNINGS ou SET ANSI_PADDINGS ne sont pas sauvegardées lorsqu'une procédure stockée est créée ou modifiée. Si la logique de la procédure stockée dépend d'un paramétrage particulier, insérez une instruction SET au début de la procédure pour assurer un paramétrage adéquat. Lorsqu'une instruction SET est exécutée à partir d'une procédure stockée, les paramètres ne restent effectifs que jusqu'à la fin de l'exécution de la procédure stockée. Les paramètres reprennent ensuite la valeur qu'ils avaient avant l'appel de la procédure stockée. Ceci permet aux clients individuels de définir les options souhaitées sans affecter la logique de la procédure stockée.

[!REMARQUE]

ANSI_WARNINGS n'est pas honoré si vous passez des paramètres à une procédure stockée, un fonction personnalisée ou si vous déclarez et définissez des variables dans une instruction de traitement. Par exemple, si une variable est définie en tant que char(3) et qu'une valeur dépassant de plus de trois caractères lui est affectée, les données se trouvent tronquées d'après la taille définie et l'instruction INSERT ou UPDATE peut alors être exécutée correctement.

Utilisation de paramètres dans les procédures stockées CLR

Les paramètres d'une procédure stockée CLR peuvent être de n'importe quel type de données scalaire système SQL Server.

Pour que le moteur de base de données crée une référence à la méthode appropriée lorsque ses capacités sont dépassées dans .NET Framework, la méthode indiquée par <method_specifier> doit présenter les caractéristiques suivantes :

  • elle doit être déclarée en tant que méthode statique ;

  • elle doit compter le même nombre de paramètres que la procédure ;

  • elle ne doit pas être un constructeur ou un destructeur de sa classe correspondante ;

  • les types de paramètres utilisés doivent être compatibles avec ceux des paramètres correspondant de la procédure SQL Server (pour plus d'informations sur la correspondance des types de données SQL Server avec ceux de .NET Framework, consultez Mappage des données de paramètres CLR) ;

  • la méthode doit retourner une valeur « void » ou de type SQLInt32, SQLInt16, System.Int32 ou System.Int16 ;

  • elle doit retourner ses paramètres par référence et non par valeur si OUTPUT est spécifié pour toute déclaration de paramètre donné.

Informations sur les procédures stockées

Pour afficher la définition d'une procédure stockée Transact-SQL, utilisez l'affichage catalogue sys.sql_modules tiré de la base de données dans laquelle se trouve la procédure.

Exemple :

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

[!REMARQUE]

Le texte d'une procédure stockée créée grâce à l'option ENCRYPTION ne peut s'afficher par le biais de l'affichage catalogue sys.sql_modules.

Pour obtenir un rapport à propos des objets référencés par une procédure, interrogez l'affichage catalogue sys.sql_expression_dependencies ou utilisez sys.dm_sql_referenced_entities et sys.dm_sql_referencing_entities.

Pour afficher les informations relatives à une procédure stockée CLR, utilisez l'affichage catalogue sys.assembly_modules tiré de la base de données dans laquelle se trouve la procédure.

Pour afficher les informations relatives aux paramètres définis dans une procédure stockée, utilisez l'affichage catalogue sys.parameters tiré de la base de données dans laquelle se trouve la procédure.

Résolution de noms différée

Vous pouvez créer des procédures stockées faisant référence à des tables qui n'existent pas encore. Au moment de la création, seul le contrôle de la syntaxe est effectué. La procédure stockée n'est compilée qu'à sa première exécution. Ce n'est qu'au moment de la compilation que la procédure stockée résout les références aux objets. Par conséquent, une procédure stockée syntaxiquement correcte faisant référence à des tables qui n'existent pas peut toujours être créée sans problème, mais son exécution échouera car les tables référencées n'existent pas. Pour plus d'informations, consultez Résolution de noms différée et compilation.

Exécution des procédures stockées

Lors de l'exécution d'une procédure stockée personnalisée, que ce soit à travers un traitement ou insérée dans un module tel qu'une autre procédure stockée personnalisée ou une fonction personnalisée, nous vous recommandons vivement de qualifier le nom de la procédure stockée à l'aide d'un nom de schéma.

Des valeurs de paramètres peuvent être fournies si une procédure stockée a été écrite pour les accepter. Cette valeur peut être une constante ou une variable. Vous ne pouvez pas utiliser un nom de fonction comme valeur de paramètre. Les variables peuvent être définies par l'utilisateur ou être des variables système, telles que @@SPID.

Pour plus d'informations, consultez Exécution de procédures stockées (Moteur de base de données).

Lorsque vous exécutez une procédure pour la première fois, elle est compilée afin d'optimiser le plan d'accès pour la récupération des données. Des exécutions postérieures de la procédure stockée peuvent entraîner la réutilisation du plan déjà généré s'il se trouve toujours dans le cache du plan du moteur de base de données. Pour plus d'informations, consultez Mise en mémoire cache et réutilisation du plan d'exécution.

Paramètres utilisant le type de données cursor

Les procédures stockées Transact-SQL ne peuvent utiliser le type de données cursor que pour les paramètres OUTPUT. Si le type de données cursor est spécifié pour un paramètre, les paramètres VARYING et OUTPUT sont alors obligatoires. Si le mot clé VARYING est spécifié pour un paramètre, le type de données doit obligatoirement être cursor et vous devez préciser le mot clé OUTPUT. Pour plus d'informations, consultez Utilisation du type de données cursor dans un paramètre OUTPUT.

Procédures stockées temporaires

Le moteur de base de données prend en charge deux types de procédures temporaires : local et global. Seule la connexion qui crée une procédure temporaire locale peut voir cette procédure. Une procédure temporaire globale est accessible par toutes les connexions. Les procédures temporaires locales sont supprimées automatiquement à la fin de la session en cours. Les procédures temporaires globales sont supprimées à la fin de la dernière session qui utilise la procédure, Pour plus d'informations, consultez Création de procédures stockées (moteur de base de données).

Exécution automatique des procédures stockées

Il est possible de lancer l'exécution automatique d'une ou plusieurs procédures stockées au démarrage de SQL Server. Les procédures stockées doivent être créées par l'administrateur système dans la base de données master et exécutée sous le rôle de serveur fixe sysadmin en tant que processus d'arrière-plan. Les procédures ne peuvent pas comprendre de paramètres d'entrée ou de sortie. Pour plus d'informations, consultez Exécution automatique des procédures stockées.

Imbrication des procédures stockées

Il est possible d'imbriquer des procédures stockées. En d'autres termes, une procédure stockée peut en appeler une autre. Le niveau d'imbrication est incrémenté lorsque la procédure appelée commence à s'exécuter, et décrémenté lorsque l'exécution est terminée. Les procédures stockées peuvent avoir 32 niveaux d'imbrication. Pour plus d'informations, consultez Imbrication de procédures stockées.

Pour estimer la taille d'une procédure stockée compilée, utilisez les Compteurs de l'Analyseur de performances décrits ci-dessous.

Nom de l'objet de l'Analyseur de performances

Nom du compteur de l'Analyseur de performances

SQLServer : Objet Plan Cache

Taux d'accès au cache

 

Pages du cache

 

Nombre d'objets cache*

* Ces compteurs sont disponibles pour diverses catégories d'objets du cache, y compris sql conforme, sql préparé, procédures, déclencheurs etc.

Pour plus d'informations, consultez SQL Server - Objet Plan Cache.

Limitations de <sql_statement>

Toute instruction SET peut être indiquée dans une procédure stockée, sauf pour les instructions SET SHOWPLAN_TEXT et SET SHOWPLAN_ALL. Elles doivent être les seules instructions d'un traitement. L'option SET choisie reste en vigueur durant l'exécution de la procédure stockée, puis retrouve sa valeur d'origine.

À l'intérieur d'une procédure stockée, il faut qualifier les noms d'objets utilisés avec les instructions DDL (Data Definition Language) telles que CREATE, ALTER et DROP, les instructions DBCC, ainsi que les instructions EXECUTE et les instructions dynamiques SQL avec le nom du schéma de l'objet si les utilisateurs, hormis le propriétaire de la procédure stockée, doivent employer la procédure stockée. Pour plus d'informations, consultez Création de procédures stockées (moteur de base de données).

Autorisations

Nécessite l'autorisation CREATE PROCEDURE dans la base de données et l'autorisation ALTER sur le schéma dans lequel la procédure est créée.

Dans le cas de procédures stockées CLR, vous devez être propriétaire de l'assembly référencé dans <method_specifier> ou disposer de l'autorisation REFERENCES sur cet assembly.

Exemples

A. Utilisation d'une procédure simple

Cette procédure stockée retourne tous les employés (prénom et nom), leur titre et le nom de leur service à partir d'une vue. Cette procédure stockée n'utilise aucun paramètre.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

La procédure stockée uspGetEmployees peut être exécutée de diverses manières, comme suit :

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. Utilisation d'une procédure simple avec des paramètres

La procédure stockée suivante ne retourne que l'employé précisé (prénom et nom), son titre et le nom de son service à partir d'une vue. Cette procédure stockée accepte les correspondances exactes pour les paramètres passés.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 

    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

La procédure stockée uspGetEmployees peut être exécutée de diverses manières, comme suit :

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

C. Utilisation d'une procédure simple avec des paramètres génériques

La procédure stockée suivante ne retourne que les employés précisés (prénom et nom), leur titre et le nom de leur service à partir d'une vue. Ce modèle de procédure stockée fait correspondre les paramètres passés ou, s'ils ne sont pas fournis, utilise les valeurs par défaut prédéfinies (dont le nom commence par la lettre D).

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

L'exécution de la procédure stockée uspGetEmployees2 peut s'effectuer selon plusieurs combinaisons. Vous trouverez ci-dessous certaines de ces combinaisons :

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

D. Renvoi de plusieurs jeux de résultats

La procédure stockée suivante renvoie deux jeux de résultats.

USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

E. Utilisation des paramètres OUTPUT

L'exemple suivant crée la procédure stockée uspGetList. Cette procédure retourne une liste de produits dont le prix ne dépasse pas un montant précisé. L'exemple illustre l'utilisation de plusieurs instructions SELECT et de plusieurs paramètres OUTPUT. Les paramètres OUTPUT permettent à une procédure externe, un traitement ou à plus d'une instruction Transact-SQL d'accéder à un ensemble de valeurs pendant l'exécution de la procédure.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Exécutez uspGetList afin de retourner la liste des produits (vélos) provenant de Adventure Works et coûtant moins de $700. Les paramètres OUTPUT correspondant à @Cost et à @ComparePrices sont utilisés en conjonction avec un langage de contrôle de flux afin de retourner un message dans la fenêtre Messages.

[!REMARQUE]

La variable OUTPUT doit être définie lors de la création de la procédure et de l'utilisation de la variable. Le nom du paramètre et le nom de la variable ne doivent pas nécessairement correspondre, contrairement au type de données et à la position du paramètre (sauf si vous utilisez @ListPrice = variable).

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Voici l'ensemble de résultats partiel :

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

F. Utilisation de l'option WITH RECOMPILE

La clause WITH RECOMPILE est utile lorsque les paramètres fournis à la procédure ne sont pas typiques et qu'un nouveau plan d'exécution ne doit pas être mis en mémoire cache ou stocké en mémoire.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

G. Utilisation de l'option WITH ENCRYPTION

L'exemple suivant crée la procédure stockée HumanResources.uspEncryptThis.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

L' option WITH ENCRYPTION permet d'éviter que la définition de la procédure stockée soit retournée, tel qu'indiqué dans les exemples suivants.

Exécutez sp_helptext :

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Voici l'ensemble des résultats.

The text for object 'HumanResources.uspEncryptThis' is encrypted.

Lancez une requête directement sur l'affichage catalogue sys.sql_modules :

USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

Voici l'ensemble des résultats.

definition
----------------------
NULL

(1 row(s) affected)

H. Utilisation de la résolution différée des noms

L'exemple suivant crée la procédure uspProc1. Il utilise la résolution différée des noms. La procédure stockée est créée même si la table référencée n'existe pas au moment de la compilation. La table doit cependant exister lorsque la procédure est exécutée.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

Pour vérifier si la procédure stockée est bien créée, lancez la requête suivante :

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

Voici l'ensemble des résultats.

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

I. Utilisation de la clause EXECUTE AS

L'exemple suivant illustre l'utilisation de la clause EXECUTE AS afin d'indiquer le contexte de sécurité dans lequel une procédure stockée peut être exécutée. Dans notre cas, l'option CALLER précise que la procédure peut être exécutée dans un contexte où l'utilisateur l'appelle.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

J. Création d'une procédure stockée CLR

Cet exemple crée la procédure stockée GetPhotoFromDB qui fait référence à la méthode GetPhotoFromDB de la classe LargeObjectBinary se trouvant dans l'assembly HandlingLOBUsingCLR . Avant la création de la procédure stockée, l'assembly HandlingLOBUsingCLR est enregistré dans la base de données locale.

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID int,
    @CurrentDirectory nvarchar(1024),
    @FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

K. Utilisation d'un paramètre OUTPUT de type cursor

Les paramètres OUTPUT de type cursor servent à renvoyer un curseur local à une procédure, au traitement appelant, à la procédure stockée ou au déclencheur.

Commencez par créer la procédure qui déclare un curseur puis l'ouvre dans la table Currency :

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

Ensuite, exécutez un traitement qui déclare une variable locale de type cursor, exécute la procédure pour affecter le curseur à la variable locale et extrait les lignes du curseur.

USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

Voir aussi

Tâches

Procédure : créer une procédure stockée (SQL Server Management Studio)

Référence

ALTER PROCEDURE (Transact-SQL)

Langage de contrôle de flux (Transact-SQL)

Types de données (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

DROP PROCEDURE (Transact-SQL)

EXECUTE (Transact-SQL)

EXECUTE AS (Transact-SQL)

EVENTDATA (Transact-SQL)

Fonctions (Transact-SQL)

sp_procoption (Transact-SQL)

sp_recompile (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.parameters (Transact-SQL)

sys.procedures (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.numbered_procedures (Transact-SQL)

sys.numbered_procedure_parameters (Transact-SQL)

OBJECT_DEFINITION (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

Concepts

Traitements

Curseurs (moteur de base de données)

Utilisation de variables et de paramètres (moteur de base de données)

Paramètres table (Moteur de base de données)

Autres ressources

Procédures stockées (moteur de base de données)