Exporter (0) Imprimer
Développer tout
Développer Réduire
Cet article a fait l'objet d'une traduction manuelle. Déplacez votre pointeur sur les phrases de l'article pour voir la version originale de ce texte. Informations supplémentaires.
Traduction
Source

CREATE PROCEDURE (Transact-SQL)

Crée une procédure stockée CLR (Common Language Runtime) ou Transact-SQL dans SQL Server. 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.

S'applique à : SQL Server (SQL Server 2008 jusqu'à la version actuelle, Base de données SQL Windows Azure (version initiale jusqu'à la version actuelle).

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

--SQL Server 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 ]
--SQL Server 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 }
[;]
--SQL Server Natively Compiled Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ] [ OUT | OUTPUT ] [READONLY] ] [ ,... n ]
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS clause
AS
{
  BEGIN ATOMIC WITH (set_option [ ,... n ] )
sql_statement [;] [ ... n ]
 [ END ]
}
 [;]

<set_option> ::=
    LANGUAGE =  [ N ] 'language'
  | TRANSACTION ISOLATION LEVEL =  { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }
  | [ DATEFIRST = number ]
  | [ DATEFORMAT = format ]
  | [ DELAYED_DURABILITY = { OFF | ON } ]
-- Windows Azure SQL Database Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name 
    [ { @parameter [ type_schema_name. ] data_type } 
    [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
AS { <sql_statement> [;][ ...n ] }
[;]
<procedure_option> ::= 
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

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

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

S'applique à : SQL Server 2008 jusqu'à SQL Server 2014.

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 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 de 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

S'applique à : SQL Server 2008 jusqu'à SQL Server 2014.

Indique que SQL Server se charge de convertir le texte d'origine de l'instruction CREATE PROCEDURE dans un format d'obscurcissement. La sortie générée par l'obscurcissement 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 obscurci. 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 clause

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

Les clauses SELF, OWNER et ‘user_name’ sont prises en charge pour l'OLTP en mémoire.

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

FOR REPLICATION

S'applique à : SQL Server 2008 jusqu'à SQL Server 2014.

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 Meilleures pratiques, Remarques d'ordre général et Limitations et restrictions.

EXTERNAL NAME assembly_name.class_name.method_name

S'applique à : SQL Server 2008 jusqu'à SQL Server 2014.

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 Remarque

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

ATOMIC WITH

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Indique l'exécution automatique d'une procédure stockée Atomic. Les modifications sont validées ou bien tous les changements sont restaurés en levant une exception. Le bloc ATOMIC WITH est requis pour les procédures stockées compilées en mode natif.

Si la procédure retourne une valeur valide (explicitement via l'instruction RETURN, ou implicitement à la fin de l'exécution), le travail effectué par la procédure est validé. Si la procédure lève une exception, le travail effectué par la procédure est restauré.

XACT_ABORT est activé (ON) par défaut dans un bloc Atomic et ne peut pas être modifié. XACT_ABORT indique si SQL Server restaure automatiquement la transaction en cours lorsqu'une instruction Transact-SQL déclenche une erreur d'exécution.

Les options SET suivantes sont toujours activées (ON) dans le bloc ATOMIC ; les options ne peuvent pas être modifiées.

  • CONCAT_NULL_YIELDS_NULL

  • QUOTED_IDENTIFIER, ARITHABORT

  • NOCOUNT

  • ANSI_NULLS

  • ANSI_WARNINGS

Les options SET ne peuvent pas être modifiées dans les blocs ATOMIC. Les options SET dans la session utilisateur ne sont pas utilisées dans l'étendue des procédures stockées compilées en mode natif. Ces options sont résolues au moment de la compilation.

Les opérations BEGIN, ROLLBACK et COMMIT ne peuvent pas être utilisées dans un bloc Atomic.

Il y a un bloc ATOMIC par procédure stockée compilée en mode natif, au niveau de l'étendue externe de la procédure. Les blocs ne peuvent pas être imbriqués. Pour plus d'informations sur les blocs Atomic, consultez Introduction aux procédures stockées compilées en mode natif.

NULL | NOT NULL

Détermine si les valeurs Null sont autorisées dans un paramètre. NULL est l'argument par défaut.

NATIVE_COMPILATION

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Indique que la procédure est compilée en mode natif. NATIVE_COMPILATION, SCHEMABINDING et EXECUTE AS peuvent être spécifiés dans n'importe quel ordre. Pour plus d'informations, consultez Introduction aux procédures stockées compilées en mode natif.

SCHEMABINDING

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Garantit que les tables référencées par une procédure ne peuvent pas être supprimées ou modifiées. SCHEMABINDING est requis dans les procédures stockées compilées en mode natif. (Pour plus d'informations, consultez Introduction aux procédures stockées compilées en mode natif.) Les restrictions SCHEMABINDING sont les mêmes que pour les fonctions définies par l'utilisateur. Pour plus d'informations, consultez la section SCHEMABINDING dans CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] ‘language’

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Équivalent à l'option de session SET LANGUAGE (Transact-SQL). LANGUAGE = [N] 'language' est requis.

TRANSACTION ISOLATION LEVEL

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Requis pour les procédures stockées compilées en mode natif. Spécifie le niveau d'isolation de la transaction pour la procédure stockée. Les options disponibles sont les suivantes :

Pour plus d'informations sur ces options, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READ

Spécifie que les instructions ne peuvent pas lire des données modifiées et non encore validées par d'autres transactions. Si une autre transaction modifie des données qui ont été lues par la transaction active, celle-ci échouera.

SERIALIZABLE

Spécifie les indications suivantes :

  • Les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions.

  • Si une autre transaction modifie des données qui ont été lues par la transaction active, celle-ci échouera.

  • Si une autre transaction insère de nouvelles lignes avec des valeurs de clés comprises dans la plage de clés lues par l'une des instructions de la transaction active, cette dernière échouera.

SNAPSHOT

Spécifie que les données lues par n'importe quelle instruction d'une transaction représenteront la version cohérente d'un point de vue transactionnel des données qui existaient au début de la transaction.

DATEFIRST = number

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Affecte au premier jour de la semaine un nombre allant de 1 à 7. DATEFIRST est facultatif. Si cela n'est pas spécifié, le paramètre est déduit en fonction du langage spécifié.

Pour plus d'informations, consultez SET DATEFIRST (Transact-SQL).

DATEFORMAT = format

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Spécifie le classement des parties de date mois, jour et année pour interpréter les chaînes de caractères date, smalldatetime, datetime, datetime2 et datetimeoffset. DATEFORMAT est facultatif. Si cela n'est pas spécifié, le paramètre est déduit en fonction du langage spécifié.

Pour plus d'informations, consultez SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }

S'applique à : SQL Server 2014 jusqu'à SQL Server 2014.

Les validations de transactions SQL Server peuvent avoir une durabilité complète, la durabilité par défaut ou une durabilité retardée.

Pour plus d'informations, consultez Contrôler la durabilité d'une transaction.

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.

  • À la place de SELECT *, utilisez les noms de colonnes requis afin d'éviter que des erreurs du Moteur de base de données n'interrompent 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 s'effectue sans problème jusqu'à ce que l'ordre ou le nombre des colonnes change dans l'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 paramètres différents pour ces options de ceux utilisés pour la connexion à l'origine de la création de 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.

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.

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

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.

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 ad hoc, Transact-SQL préparé, procédures, déclencheurs, etc. Pour plus d'informations, consultez SQL Server - Objet Plan Cache.

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.

Pour plus d'informations, consultez Introduction aux procédures stockées compilées en mode natif.

L'exemple suivant montre comment créer une procédure stockée pour l'utiliser avec des tables mémoire optimisées :

CREATE PROCEDURE usp_add_kitchen @dept_id int, @kitchen_count int NOT NULL
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

  UPDATE dbo.Departments
  SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count
  WHERE id = @dept_id
END;
GO

Utilisez des procédures stockées compilées en mode natif pour accéder aux tables mémoire optimisées. Pour plus d'informations, consultez OLTP en mémoire (optimisation en mémoire).

Seules les valeurs par défaut des paramètres fournies par les littéraux sont prises en charge dans CREATE PROCEDURE pour les procédures stockées compilées en mode natif. Les paramètres de sortie sont également pris en charge.

Une procédure créée sans NATIVE_COMPILATION ne peut pas être modifiée en une procédure stockée compilée en mode natif. ALTER PROCEDURE n'est pas pris en charge pour les procédures stockées compilées en mode natif.

Pour la programmabilité dans les procédures stockées compilées en mode natif, la surface d'exposition de requête prise en charge et les opérateurs, consultez Constructions prises en charge dans les procédures stockées compilées en mode natif.

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 de la base de données AdventureWorks2012. Cette procédure n'utilise aucun paramètre. L'exemple illustre ensuite trois méthodes permettant d'exécuter la procédure.

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

SELECT * FROM HumanResources.vEmployeeDepartment;

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.

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.

S'applique à : SQL Server 2008 jusqu'à SQL Server 2014.

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.

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    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).

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

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 lot ou à plus d'une instruction Transact-SQL d'accéder à un ensemble de valeurs pendant l'exécution de la procédure.

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.

RemarqueRemarque

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.

/* 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 :

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.

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.

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.

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.

S'applique à : SQL Server 2008 jusqu'à SQL Server 2014.

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 :

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.

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;

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.

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;

Cela vous a-t-il été utile ?
(1500 caractères restants)
Merci pour vos suggestions.

Ajouts de la communauté

AJOUTER
Microsoft réalise une enquête en ligne pour recueillir votre opinion sur le site Web de MSDN. Si vous choisissez d’y participer, cette enquête en ligne vous sera présentée lorsque vous quitterez le site Web de MSDN.

Si vous souhaitez y participer,
Afficher:
© 2014 Microsoft