Partager via

CREATE PROCEDURE (Transact-SQL)

  • Article

Crée une procédure stockée CLR (Common Language Runtime) ou Transact-SQL dans SQL Server 2012. Les procédures stockées ressemblent aux procédures d'autres langages de programmation, car elles peuvent :

  • accepter des paramètres d'entrée et retourner plusieurs valeurs sous la forme de paramètres de sortie à la procédure ou au lot appelant ;

  • contenir des instructions de programmation qui exécutent des opérations dans la base de données, y compris l'appel d'autres procédures ;

  • retourner une valeur d'état à une procédure ou à un lot appelant pour indiquer une réussite ou un échec (et la raison de l'échec).

Utilisez cette instruction pour créer une procédure permanente dans la base de données actuelle ou une procédure temporaire dans la base de données tempdb.

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

Syntaxe

--Transact-SQL Stored Procedure Syntax
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 { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

--CLR Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Arguments

  • schema_name
    Nom du schéma auquel appartient la procédure. Les procédures sont liées à un schéma. Si un nom de schéma n'est pas précisé lors de la création de la procédure, le schéma par défaut de l'utilisateur chargé de créer la procédure est automatiquement utilisé.

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

    Évitez l'utilisation du préfixe sp_ dans le nom des procédures. En effet, ce préfixe est utilisé par SQL Server pour faire référence aux procédures système. L'utilisation de ce préfixe peut entraîner l'échec du code de l'application s'il existe une procédure système portant le même nom.

    Des 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. Une procédure temporaire locale n'est visible que par la connexion qui l'a créée et est automatiquement supprimée au moment de la déconnexion. Une procédure temporaire globale est disponible pour toutes les connexions et est supprimée à la fin de la dernière session qui l'utilise. Des noms temporaires ne peuvent pas être indiqués pour les procédures CLR.

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

  • ; number
    Entier facultatif qui regroupe les procédures de 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.

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

    Les procédures numérotées ne peuvent pas utiliser les type CLR définis par l'utilisateur ou xml et ne peuvent pas être utilisées dans un repère de plan.

  • @ parameter
    Paramètre déclaré dans la procédure. 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.

    Un ou plusieurs paramètres peuvent être déclarés, dans la limite de 2 100. 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. Si une procédure contient des paramètres table et que le paramètre n'est pas indiqué dans l'appel, une table vide est passée. 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).

    Il n'est pas possible de déclarer des paramètres si FOR REPLICATION est spécifié.

  • [ type_schema_name**.** ] data_type
    Type de données du paramètre et du schéma auquel le type de données appartient.

    Instructions pour les procédures Transact-SQL :

    • Tous les types de données Transact-SQL peuvent être utilisés en tant que paramètres.

    • Vous pouvez utiliser le type de table défini par l'utilisateur pour créer des paramètres table. Les paramètres table ne peuvent être spécifiés que comme paramètres INPUT et ils doivent être accompagnés du mot clé READONLY. Pour plus d'informations, consultez Utiliser les paramètres table (Moteur de base de données).

    • Les types de données cursor ne peuvent être que des paramètres OUTPUT et ils doivent être accompagnés du mot clé VARYING.

    Instructions pour les procédures CLR :

    • Tous les types de données SQL Server natifs qui ont un équivalent en code managé peuvent être utilisés en tant que paramètres. 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 et leur syntaxe, consultez Types de données (Transact-SQL).

    • Les types de données cursor ou table ne peuvent pas être utilisés comme paramètres.

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

  • VARYING
    Spécifie le jeu de résultats pris en charge comme paramètre de sortie. Ce paramètre est construit dynamiquement par la procédure ; il se peut donc que son contenu varie. S'applique uniquement aux paramètres de type cursor. Cette option n'est pas valide pour les procédures CLR.

  • default
    Valeur par défaut pour un paramètre. Si une valeur par défaut est définie pour un paramètre, 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 il peut s'agir de la valeur NULL. La valeur constante peut être exprimée sous la forme d'un caractère générique, rendant ainsi possible l'utilisation du mot clé LIKE lors de la transmission du paramètre à la procédure. Voir exemple C ci-dessous.

    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.

  • OUT | OUTPUT
    Indique que le paramètre est un paramètre de sortie. Utilisez les paramètres OUTPUT pour retourner des valeurs à l'appelant de la procédure. Les paramètres text, ntext et image ne peuvent pas être utilisés comme paramètres OUTPUT, sauf si la procédure est une procédure CLR. Un paramètre de sortie peut être un espace réservé pour curseur, sauf si la procédure correspond à une procédure CLR (Common Language Runtime). Un type de données table ne peut pas être spécifié comme paramètre OUTPUT d'une procédure.

  • 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, READONLY doit être spécifié.

  • RECOMPILE
    Indique que le Moteur de base de données n'utilise pas le cache pour le plan de requête de cette procédure, ce qui oblige celle-ci à être recompilée chaque fois qu'elle est exécutée. Pour plus d'informations sur les raisons d'une recompilation forcée, consultez Recompiler une procédure stockée. Cette option ne peut pas être utilisée lorsque FOR REPLICATION est spécifié ou pour les procédures CLR (Common Language Runtime).

    Pour ordonner au Moteur de base de données d'annuler les plans de requête relatifs à des requêtes individuelles au sein d'une procédure, utilisez dans ce cas l'indicateur de requête RECOMPILE dans la définition de la requête. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL).

  • ENCRYPTION
    Indique que SQL Server se charge de convertir le texte d'origine 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 un 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. En outre, les utilisateurs qui peuvent attacher un débogueur au processus serveur peuvent également récupérer la procédure déchiffrée à partir de la mémoire au moment de 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 CLR.

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

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

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

  • FOR REPLICATION
    Spécifie que la procédure est créée en vue d'une réplication. Par conséquent, elle ne peut pas être exécutée sur l'Abonné. Une procédure créée avec l'option FOR REPLICATION est utilisée comme filtre de procédure et n'est exécutée que lors de la réplication. Il n'est pas possible de déclarer des paramètres si FOR REPLICATION est spécifié. FOR REPLICATION ne peut pas être précisé pour une utilisation avec des procédures CLR. L'option RECOMPILE est ignorée pour les procédures créées avec l'option FOR REPLICATION.

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

  • { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
    Une ou plusieurs instructions Transact-SQL comprenant le corps de la procédure. Vous pouvez utiliser les mots clés facultatifs BEGIN et END pour délimiter les instructions. Pour plus d'informations, consultez les sections suivantes intitulées Recommandations, Remarques d'ordre général et Limitations et restrictions.

  • EXTERNAL NAME assembly_name**.class_name.method_name
    Spécifie la méthode d'un assembly .NET Framework pour une procédure CLR à référencer. class_name doit être un identificateur SQL Server valide et doit exister en tant que classe dans 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.

    Par défaut, SQL Server ne peut pas exécuter du code CLR. Vous pouvez créer, modifier et supprimer des objets d'une base de données qui font référence à des modules CLR (Common Language Runtime) ; cependant, vous ne pouvez pas exécuter ces références dans SQL Server tant que vous n'avez pas activé l'option CLR activé. Pour activer cette option, utilisez sp_configure.

    [!REMARQUE]

    Les procédures CLR ne sont pas prises en charge dans une base de données à relation contenant-contenu.

Recommandations

Les suggestions fournies dans cette section peuvent vous aider à améliorer les performances des procédures, même si cette liste n'est pas exhaustive.

  • Utilisez l'instruction SET NOCOUNT ON comme première instruction dans le corps de la procédure. Autrement dit, placez-la juste après le mot clé AS. Cela permet de désactiver les messages renvoyés par SQL Server au client une fois les instructions SELECT, INSERT, UPDATE, MERGE et DELETE exécutées. Les performances globales de la base de données et de l'application peuvent être améliorées en éliminant toute surcharge réseau inutile. Pour plus d'informations, consultez SET NOCOUNT (Transact-SQL).

  • Utilisez des noms de schémas lorsque vous créez ou référencez des objets de base de données dans la procédure. Il faut moins de temps au Moteur de base de données pour résoudre les noms d'objets s'il n'a pas à rechercher dans plusieurs schémas. Cela évite également les problèmes d'autorisation et d'accès causés par le schéma par défaut d'un utilisateur qui est affecté lors de la création d'objets sans spécifier le schéma.

  • Évitez les fonctions de renvoi à la ligne pour les fonctions autour des colonnes spécifiées dans les clauses WHERE et JOIN. Les colonnes seront ainsi non déterministes, ce qui empêche le processeur de requêtes d'utiliser des index.

  • Évitez d'utiliser des fonctions scalaires dans des instructions SELECT qui retournent un grand nombre de lignes de données. Étant donné que la fonction scalaire doit être appliquée à chaque ligne, le comportement s'apparente à un traitement par ligne et nuit aux performances.

  • Évitez l'utilisation de SELECT *. Spécifiez à la place les noms de colonnes requis. Cela peut éviter certaines erreurs du Moteur de base de données qui arrêtent l'exécution de la procédure. Par exemple, l'exécution d'une instruction SELECT * qui retourne des données depuis une table contenant 12 colonnes, puis insère ces données dans une table temporaire de 12 colonnes également s'effectue sans problème jusqu'à ce que l'ordre ou le nombre des colonnes change dans une des tables.

  • Évitez de traiter ou de retourner un trop grand nombre de données. Restreignez les résultats le plus tôt possible dans le code de la procédure afin que les opérations suivantes effectuées par la procédure impliquent le jeu de données le plus petit possible. Envoyez uniquement les données essentielles à l'application cliente. Cela s'avère plus efficace que l'envoi de données supplémentaires sur le réseau et l'obligation par l'application cliente de traiter inutilement des jeux de résultats volumineux.

  • Utilisez des transactions explicites avec BEGIN/END TRANSACTION et gardez les transactions aussi courtes que possible. Les transactions plus longues entraînent un verrouillage plus long des enregistrements et un plus grand risque de blocage.

  • Utilisez la fonction Transact-SQL TRY…CATCH pour la gestion des erreurs au sein d'une procédure. TRY…CATCH peut encapsuler un bloc entier d'instructions Transact-SQL. Cela entraîne non seulement une moindre diminution des performances, mais contribue également à améliorer la création de rapports d'erreurs avec une programmation beaucoup moins lourde.

  • Utilisez le mot clé DEFAULT sur toutes les colonnes de table qui sont référencées par des instructions Transact-SQL CREATE TABLE ou ALTER TABLE dans le corps de la procédure. Cela évite de passer une valeur NULL aux colonnes qui n'autorisent pas ce type de valeur.

  • Utilisez 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 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.

  • Utilisez des instructions de modification qui convertissent les valeurs Null et incluez une logique éliminant des requêtes les lignes contenant des valeurs Null. Sachez que dans Transact-SQL, NULL n'est pas une valeur vide ou « Nothing ». Il s'agit d'un espace réservé à une valeur inconnue et peut être à l'origine d'un comportement inattendu, notamment lors de l'interrogation de jeux de résultats ou de l'utilisation de fonctions AGGREGATE.

  • Utilisez l'opérateur UNION ALL au lieu des opérateurs UNION ou OR, sauf si vous avez besoin de valeurs distinctes. L'opérateur UNION ALL requiert moins de charge de traitement étant donné que les doublons ne sont pas filtrés dans le jeu de résultats.

Remarques d'ordre général

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

Les variables spécifiées dans la procédure peuvent être des variables système ou des variables définies par l'utilisateur, telles que @@SPID.

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 de la procédure postérieures 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.

Il est possible de lancer l'exécution automatique d'une ou plusieurs procédures au démarrage de SQL Server. Les procédures doivent être créées par l'administrateur système dans la base de données master et exécutées sous le rôle 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écuter une procédure stockée.

Les procédures sont imbriquées lorsqu'une procédure en appelle une autre ou exécute du code managé en faisant référence à une routine, un type ou un agrégat CLR. Vous pouvez imbriquer des procédures et des références au code managé jusqu'à 32 niveaux. L'imbrication augmente d'un niveau lorsque la procédure appelée ou la référence au code managé commence à s'exécuter, et diminue d'un niveau lorsque son exécution est terminée. Les méthodes appelées à partir du code managé n'entrent pas en compte dans la limite de niveau d'imbrication. Toutefois, lorsqu'une procédure stockée CLR exécute des opérations d'accès aux données par le biais du fournisseur managé de SQL Server, un niveau d'imbrication supplémentaire est ajouté à la transition du code managé vers SQL.

Au-delà du niveau d'imbrication maximal, toute la chaîne d'appels échoue. Vous pouvez utiliser la fonction @@NESTLEVEL pour retourner le niveau d'imbrication de l'exécution de procédure stockée actuelle.

Interopérabilité

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 Transact-SQL. Ces paramètres d'origine sont utilisés lors de l'exécution de la procédure. 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.

D'autres options SET, telles que SET ARITHABORT, SET ANSI_WARNINGS ou SET ANSI_PADDINGS ne sont pas sauvegardées lorsqu'une procédure est créée ou modifiée. Si la logique de la procédure 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, les paramètres ne restent effectifs que jusqu'à la fin de l'exécution de la procédure. Les paramètres reprennent ensuite la valeur qu'ils avaient avant l'appel de la procédure. Ceci permet aux clients individuels de définir les options souhaitées sans affecter la logique de la procédure.

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

[!REMARQUE]

L'option SET ANSI_WARNINGS n'est pas reconnue lors d'une transmission de paramètres dans une procédure ou dans une fonction définie par l'utilisateur, ou bien lors de la déclaration et de la définition de variables dans une instruction par lot. 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.

Limitations et restrictions

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

Les instructions suivantes ne peuvent pas être utilisées dans le corps d'une procédure stockée.

CREATE AGGREGATE

CREATE SCHEMA

SET SHOWPLAN_TEXT

CREATE DEFAULT

CREATE ou ALTER TRIGGER

SET SHOWPLAN_XML

CREATE ou ALTER FUNCTION

CREATE ou ALTER VIEW

USE database_name

CREATE ou ALTER PROCEDURE

SET PARSEONLY

CREATE RULE

SET SHOWPLAN_ALL

Une procédure peut faire 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 n'est compilée qu'à sa première exécution. Ce n'est qu'au moment de la compilation que la procédure résout les références aux objets. Par conséquent, une procédure 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.

Vous ne pouvez pas spécifier un nom de fonction comme valeur par défaut d'un paramètre ou comme valeur transmise à un paramètre lors de l'exécution d'une procédure. En revanche, vous pouvez passer une fonction comme variable, comme indiqué dans l'exemple suivant.

-- Passing the function value as a variable.
DECLARE @CheckDate datetime = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; 
GO

Si la procédure apporte des modifications sur une instance distante de SQL Server, les modifications ne peuvent pas être restaurées. Les procédures distantes ne font pas partie des transactions.

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 le .NET Framework, la méthode indiquée par la clause EXTERNAL NAME 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 ;

  • 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.

Métadonnées

Le tableau suivant répertorie les affichages catalogue et vues de gestion dynamique que vous pouvez utiliser pour retourner des informations sur les procédures stockées.

Vue

Description

sys.sql_modules

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

sys.assembly_modules

Retourne des informations sur une procédure CLR.

sys.parameters

Retourne des informations sur les paramètres définis dans une procédure

sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities

Retourne les objets référencés par une procédure.

Pour estimer la taille d'une procédure 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 Transact-SQL conforme, Transact-SQL préparé, procédures, déclencheurs etc. Pour plus d'informations, consultez SQL Server - Objet Plan Cache.

Sécurité

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, ou nécessite l'appartenance au rôle de base de données fixe db_ddladmin.

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

Exemples

Catégorie

Éléments syntaxiques proposés

Syntaxe de base

CREATE PROCEDURE

Passage de paramètres

@parameter • = par défaut • OUTPUT • type de paramètre table • CURSOR VARYING

Modification des données à l'aide d'une procédure stockée

UPDATE

Gestion des erreurs

TRY…CATCH

Obscurcissement de la définition de procédure

WITH ENCRYPTION

Recompilation forcée de la procédure

WITH RECOMPILE

Définition du contexte de sécurité

EXECUTE AS

Syntaxe de base

Les exemples fournis dans cette section présentent les fonctionnalités de base de l'instruction CREATE PROCEDURE en utilisant la syntaxe minimale requise.

A.Création d'une procédure Transact-SQL simple

L'exemple suivant crée une procédure stockée qui retourne tous les employés (prénom et nom), leur titre et le nom de leur service à partir d'une vue. Cette procédure n'utilise aucun paramètre. L'exemple illustre ensuite trois méthodes permettant d'exécuter la procédure.

USE AdventureWorks2012;
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 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.Renvoi de plusieurs jeux de résultats

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

USE AdventureWorks2012;
GO
CREATE PROCEDURE dbo.uspMultipleResults 
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

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

Cet exemple crée la procédure 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, l'assembly HandlingLOBUsingCLR est inscrit 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

Passage de paramètres

Les exemples présentés dans cette section montrent comment utiliser des paramètres d'entrée et de sortie pour transmettre des valeurs vers et à partir d'une procédure stockée.

A.Création d'une procédure avec des paramètres d'entrée

L'exemple suivant crée une procédure stockée qui retourne des informations pour un employé spécifique en passant des valeurs pour le prénom et le nom de l'employé. Cette procédure accepte uniquement les correspondances exactes pour les paramètres passés.

USE AdventureWorks2012;
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 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';

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

L'exemple suivant crée une procédure stockée qui retourne des informations pour des employés en passant des valeurs complètes ou partielles pour le prénom et le nom de l'employé. Ce modèle de procédure 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 AdventureWorks2012;
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 uspGetEmployees2 peut s'effectuer selon plusieurs combinaisons. Vous trouverez ci-dessous certaines des combinaisons possibles.

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%';

C.Utilisation des paramètres OUTPUT

L'exemple suivant crée la procédure 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 AdventureWorks2012;
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 le jeu 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.

D.Utilisation d'un paramètre table

L'exemple suivant utilise un type de paramètre table pour insérer plusieurs lignes dans une table. L'exemple crée le type de paramètre, déclare une variable de table pour y faire référence, remplit la liste de paramètres, puis passe les valeurs à une procédure stockée. La procédure stockée utilise les valeurs pour insérer plusieurs lignes dans une table.

USE AdventureWorks2012;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2012].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2012].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

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

L'exemple suivant utilise le paramètres de type cursor OUTPUT pour renvoyer un curseur local à une procédure, au lot appelant, à la procédure ou au déclencheur.

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

USE AdventureWorks2012;
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 lot 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 AdventureWorks2012;
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

Modification de données à l'aide d'une procédure stockée

Les exemples présentés dans cette section montrent comment insérer ou modifier des données dans des tables ou des vues en incluant une instruction DML (Data Manipulation Language) dans la définition de la procédure.

A.Utilisation de l'instruction UPDATE dans une procédure stockée

L'exemple ci-dessous utilise une instruction UPDATE dans une procédure stockée. La procédure accepte un paramètre d'entrée, @NewHours et un paramètre de sortie @RowCount. La valeur du paramètre @NewHours est utilisée dans l'instruction UPDATE pour mettre à jour la colonne VacationHours de la table HumanResources.Employee. Le paramètre de sortie @RowCount est utilisé pour retourner le nombre de lignes affectées à une variable locale. Une expression CASE est utilisée dans la clause SET pour déterminer de manière conditionnelle la valeur définie pour VacationHours. Lorsque l'employé est payé à l'heure (SalariedFlag = 0), VacationHours est défini avec le nombre actuel d'heures plus la valeur spécifiée dans @NewHours ; sinon, VacationHours est défini avec la valeur spécifiée dans @NewHours.

USE AdventureWorks2012;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
GO

EXEC HumanResources.Update_VacationHours 40;

Gestion des erreurs

Les exemples de cette section présentent des méthodes pour gérer les erreurs qui peuvent se produire lorsque la procédure stockée est exécutée.

Utilisation de TRY…CATCH

L'exemple suivant illustre l'utilisation de la construction TRY… CATCH pour retourner des informations sur les erreurs interceptées pendant l'exécution d'une procédure stockée.

USE AdventureWorks2012;
GO

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS
SET NOCOUNT ON;
BEGIN TRY
   BEGIN TRANSACTION 
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;

   -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;

/* Intentionally generate an error by reversing the order in which rows are deleted from the
   parent and child tables. This change does not cause an error when the procedure
   definition is altered, but produces an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS

BEGIN TRY
   BEGIN TRANSACTION 
      -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT TRANSACTION

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;

DROP PROCEDURE Production.uspDeleteWorkOrder;

Obscurcissement de la définition de procédure

Les exemples de cette section illustrent comment obscurcir la définition de la procédure stockée.

A.Utilisation de l'option WITH ENCRYPTION

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

USE AdventureWorks2012;
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 BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

L'option WITH ENCRYPTION obscurcit la définition de la procédure lors de l'interrogation du catalogue système ou de l'utilisation de fonctions de métadonnées, comme 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 AdventureWorks2012;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

Voici l'ensemble des résultats.

definition

--------------------------------

NULL

Recompilation forcée de la procédure

Les exemples de cette section utilisent la clause WITH RECOMPILE pour forcer la recompilation de la procédure chaque fois qu'elle est exécutée.

A.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 AdventureWorks2012;
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.BusinessEntityID = pv.BusinessEntityID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

Définition du contexte de sécurité

Les exemples de cette section utilisent la clause EXECUTE AS pour définir le contexte de sécurité dans lequel la procédure stockée est exécutée.

A.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 peut être exécutée. Dans cet exemple, l'option CALLER précise que la procédure peut être exécutée dans le contexte de l'utilisateur qui l'appelle.

USE AdventureWorks2012;
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 'Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

B.Création de jeux d'autorisations personnalisés

L'exemple suivant utilise EXECUTE AS pour créer des autorisations personnalisées pour une opération de base de données. Il n'est pas possible d'accorder des autorisations à certaines opérations, telles que TRUNCATE TABLE. En intégrant l'instruction TRUNCATE TABLE dans une procédure stockée et en spécifiant que cette procédure s'exécute en tant qu'utilisateur disposant des autorisations de modifier la table, vous pouvez étendre les autorisations de tronquer la table à l'utilisateur auquel vous accordez les autorisations EXECUTE sur la procédure.

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

Voir aussi

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)

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

Curseurs

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

Créer une procédure stockée

Utiliser les paramètres table (Moteur de base de données)