CREATE PROCEDURE (Transact-SQL)

 

CETTE RUBRIQUE S’APPLIQUE À : ouiSQL Server (à partir de la version 2008)ouiAzure SQL DatabaseouiAzure SQL Data WarehouseouiParallel Data Warehouse

Crée un Transact-SQL ou common language runtime (CLR) procédure stockée dans SQL Server, Base de données Azure SQL, Azure SQL Data Warehouse et Parallel Data Warehouse. 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 d’une procédure temporaire dans le tempdb base de données.

System_CAPS_ICON_note.jpg Remarque


L’intégration du CLR .NET Framework dans SQL Server est décrite dans cette rubrique. Intégration du CLR ne s’applique pas à Azure Base de données SQL.

Atteindre exemples simples pour ignorer les détails de la syntaxe et obtenir un exemple de base de procédure stockée.

Topic link icon Conventions de la syntaxe Transact-SQL

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database  
  
CREATE [ OR ALTER ] { 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 ]  

-- Transact-SQL Syntax for CLR Stored Procedures  
  
CREATE [ OR ALTER ] { 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 }  
[;]  

-- Transact-SQL Syntax for Natively Compiled Stored Procedures  
  
CREATE [ OR ALTER ] { 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 } ]  

-- Transact-SQL Syntax for Stored Procedures in Azure SQL Data Warehouse and Parallel Data Warehouse  
  
-- Create a stored procedure   
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name  
    [ { @parameterdata_type } [ OUT | OUTPUT ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [;][ ,...n ] [ END ] }  
[;]  

OU ALTER

S’applique aux: Azure Base de données SQL, SQL Server (commençant par SQL Server 2016 SP1).

Modifie la procédure s’il existe déjà.

schema_name
Le 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é.

nom_procédure
Le nom de la procédure. Les noms des procédures doivent respecter les règles de identificateurs et doit être unique au sein du schéma.

Évitez d’utiliser le sp_ préfixe lorsque vous nommez 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.

Les procédures temporaires locales ou globales peuvent être créés à l’aide d’un signe dièse (#) avant de nom_procédure (# nom_procédure) pour les procédures temporaires locales et deux signes dièse pour les procédures temporaires globales (## nom_procédure). 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 aux: SQL Server 2008 via SQL Server 2016 et Base de données Azure SQL.

Entier facultatif qui est utilisé pour regrouper 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.

System_CAPS_ICON_note.jpg 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é.

Procédures numérotées ne peuvent pas utiliser le xml ou les types de CLR défini par l’utilisateur et ne peut pas être utilisé dans un repère de plan.

@paramètre
Paramètre déclaré dans la procédure. Spécifiez un nom de paramètre à l’aide de l’arobase (@) comme premier caractère. Le nom du paramètre doit respecter les règles de 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 paramètres tableet le paramètre est manquant 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.

Recommandations en matière de Transact-SQL procédures:

  • Tous les Transact-SQL les types de données 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 Use Table-Valued paramètres (moteur de base de données)

  • curseur les types de données ne peut être que les paramètres de sortie et doit être accompagnés par le 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 CLR et SQL Server les types de données système, consultez de mappage de données de paramètre CLR. Pour plus d’informations sur SQL Server les types de données système et leur syntaxe, consultez des Types de données (Transact-SQL).

  • Table multi-instructions ou curseur les types de données ne peut pas être utilisés en tant que 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 curseur paramètres. Cette option n'est pas valide pour les procédures CLR.

par défaut
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.

Valeurs par défaut sont enregistrés dans le sys.parameters.default colonne 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 les valeurs à la procédure appelante. texte, ntext, et image paramètres ne peuvent pas être utilisés comme paramètres de sortie, 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 ne met pas en cache un plan de requête pour cette procédure, en la forçant à être compilé 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 demander à le Moteur de base de données pour ignorer les plans de requête pour les requêtes individuelles au sein d’une procédure, utilisez 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 aux: SQL Server (SQL Server 2008 via SQL Server 2016), Base de données Azure SQL.

Indique que SQL Server convertit le texte d’origine de l’instruction CREATE PROCEDURE dans un format obscurci. La sortie générée par l'obfuscation n'est pas visible directement dans les affichages 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. Toutefois, le texte est disponible pour les utilisateurs privilégiés qui accèdent aux tables système via la port DAC ou accéder directement aux fichiers de base de données. Les utilisateurs qui peuvent attacher un débogueur au processus serveur peuvent également récupérer la procédure déchiffrée de la mémoire à l'exécution. Pour plus d’informations sur l’accès aux métadonnées système, consultez la page Configuration de 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.

Pour les procédures stockées compilées en mode natif, démarrage SQL Server 2016 et dans Base de données Azure SQL, n’est pas limité sur EXECUTE AS clause. Dans SQL Server 2014 le SELF, OWNER et 'nom_utilisateur' clauses sont pris en charge avec des procédures stockées compilées en mode natif.

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

FOR REPLICATION

S’applique aux: SQL Server (SQL Server 2008 via SQL Server 2016), Base de données Azure SQL.

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 de réplication auront un type d’objet RF dans sys.objects et 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.

NOM externe assembly_name. class_name. nom_méthode

Applies to: SQL Server 2008 through SQL Server 2016, Base de données SQL.

Spécifie la méthode d'un assembly .NET Framework pour une procédure CLR à référencer. CLASS_NAME doit être un SQL Server identificateur et doit exister en tant que classe dans l’assembly. Si la classe a un nom qualifié d’espace de noms qui utilise une période (.) pour séparer les parties de l’espace de noms, le nom de classe doit être délimité par des crochets ([]) ou des guillemets (» «). 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 de base de données qui référencent des modules de common language runtime ; Toutefois, vous ne pouvez pas exécuter ces références dans SQL Server jusqu'à ce que vous activiez le option clr enabled. Pour activer l’option, utilisez sp_configure.

System_CAPS_ICON_note.jpg Remarque


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

ATOMIC WITH

S’applique aux: SQL Server 2014 via SQL Server 2016 et Base de données Azure SQL.

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 en mode natif procédures stockées compilées.

NULL | NON 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 aux: SQL Server 2014 via SQL Server 2016 et Base de données Azure SQL.

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 en mode natif procédures stockées compilées.

SCHEMABINDING

S’applique aux: SQL Server 2014 via SQL Server 2016 et Base de données Azure SQL.

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 en mode natif procédures stockées compilées.) 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 aux: SQL Server 2014 via SQL Server 2016 et Base de données Azure SQL.

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

TRANSACTION ISOLATION LEVEL

S’applique aux: SQL Server 2014 via SQL Server 2016 et Base de données Azure SQL.

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 la page 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 en cours, la transaction en cours échoue.

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 toute instruction dans une transaction seront la version cohérente des données qui existaient au début de la transaction.

DATEFIRST = nombre

S’applique aux: SQL Server 2014 via SQL Server 2016 et Base de données Azure SQL.

Spécifie le premier jour de la semaine à un nombre compris entre 1 et 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 aux: SQL Server 2014 via SQL Server 2016 et Base de données Azure SQL.

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 aux: SQL Server 2014 via SQL Server 2016 et Base de données Azure SQL.

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ôle la durabilité des transactions.

Pour vous aider à commencer, voici deux exemples rapides :
SELECT DB_NAME() AS ThisDB;Retourne le nom de la base de données actuelle.
Vous pouvez encapsuler cette instruction dans une procédure stockée, telles que :

CREATE PROC What_DB_is_this     
AS   
SELECT DB_NAME() AS ThisDB; 

Appelez la procédure stockée avec l’instruction :EXEC What_DB_is_this;

Un peu plus complexe, est de fournir un paramètre d’entrée pour rendre la procédure plus flexible. Exemple :

CREATE PROC What_DB_is_that @ID int   
AS    
SELECT DB_NAME(@ID) AS ThatDB;   

Fournir un numéro d’identification de base de données lorsque vous appelez la procédure. Par exemple EXEC What_DB_is_that 2; renvoie tempdb.

Consultez la page exemples vers la fin de cette rubrique pour obtenir des exemples plus nombreux.

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, une instruction SELECT * instruction qui retourne des données à partir d’une table contenant 12 colonnes et insère ensuite ces données dans une table temporaire de 12 colonnes réussiront jusqu'à ce que le nombre ou la modification de l’ordre des colonnes dans les 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.

  • Utiliser des transactions explicites à l’aide de BEGIN/COMMIT TRANSACTION et conserver autant que possible les transactions. 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 défini par l’utilisateur ou des variables système, tel 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 le master de base de données et sont exécutées dans le sysadmin rôle serveur fixe comme un processus en 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 le @@NESTLEVEL fonction pour renvoyer le niveau d’imbrication de l’exécution d’une procédure stockée en cours.

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.

System_CAPS_ICON_note.jpg 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), puis définissez une valeur supérieure à trois caractères, les données sont tronquées à la taille définie et l’insertion ou instruction de mise à jour réussit.

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 AGGREGATECREATE SCHEMASET SHOWPLAN_TEXT
CREATE DEFAULTCREATE ou ALTER TRIGGERSET SHOWPLAN_XML
CREATE ou ALTER FUNCTIONCREATE ou ALTER VIEWUtilisez nom_base_de_données
CREATE ou ALTER PROCEDURESET PARSEONLY
CREATE RULESET 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 SQL Server des types de données pour le .NET Framework des types de données, consultez de mappage de données de paramètre 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.

AffichageDescription
Sys.sql_modulesRetourne la définition d'une procédure Transact-SQL. Le texte d’une procédure créée avec l’option ENCRYPTION ne peut pas être affiché à l’aide de la sys.sql_modules affichage catalogue.
Sys.assembly_modulesRetourne des informations sur une procédure CLR.
Sys.ParametersRetourne 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_entitiesRetourne 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 performancesNom du compteur de l'Analyseur de performances
SQLServer : Objet Plan CacheTaux 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 du Cache de Plan.

Permissions

Requiert CREATE PROCEDURE autorisation dans la base de données et ALTER autorisation sur le schéma dans lequel la procédure est en cours de création ou nécessite l’appartenance dans le db_ddladmin rôle de base de données fixe.

Procédures stockées CLR, vous devez être propriétaire de l’assembly référencé dans la clause EXTERNAL NAME, ou références autorisation sur cet assembly.

Tables optimisées en mémoire sont accessibles via des procédures stockées traditionnelles et compilées en mode natif. Les procédures natifs sont le plus souvent le moyen le plus efficace. Pour plus d’informations, consultez en mode natif procédures stockées compilées.

L’exemple suivant montre comment créer une procédure stockée compilée en mode natif qui accède à une table optimisée en mémoire dbo.Departments:

CREATE PROCEDURE dbo.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  

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.

Pour une discussion sur la programmabilité dans les procédures stockées compilées en mode natif, prise en charge de la surface d’exposition de requête et les opérateurs, consultez les fonctionnalités prises en charge en mode natif compilé les modules de T-SQL.

CatégorieÉléments syntaxiques proposés
Syntaxe de baseCREATE PROCEDURE
Passage de paramètres@parameter
  • = par défaut
  • SORTIE
  type de paramètre table •
  • CURSOR VARYING
Modification des données à l’aide d’une procédure stockéeUPDATE
Gestion des erreursTRY…CATCH
Masquage de la définition de procédureWITH ENCRYPTION
Forcer la recompilation de procédureWITH 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;  
  

Le uspGetEmployees procédure peut être exécutée 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

L’exemple suivant crée la GetPhotoFromDB procédure qui fait référence à la GetPhotoFromDB procédé de la LargeObjectBinary classe dans le HandlingLOBUsingCLR assembly. Avant que la procédure est créée, le HandlingLOBUsingCLR assembly est inscrit dans la base de données locale.

S’applique aux: SQL Server 2008 via SQL Server 2016, Base de données SQL (si vous utilisez un assembly créé à partir de assembly_bits.
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.

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

Le uspGetEmployees procédure peut être exécutée 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';  
  

E. 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, si ne 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;  
  

Le uspGetEmployees2 procédure peut être exécutée 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%';  
  

F. 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. Le OUTPUT paramètres @Cost et @ComparePrices sont utilisés avec le langage de contrôle de flux pour retourner un message dans le Messages fenêtre.

System_CAPS_ICON_note.jpg 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 est inutile de correspondent ; Toutefois, le type de données et le positionnement du paramètre doivent correspondre à, sauf si @ListPrice = variable est utilisé.

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.

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

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

I. 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. Le @NewHours la valeur du paramètre est utilisée dans l’instruction UPDATE pour mettre à jour la colonne VacationHours dans 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.

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

Masquage de la définition de procédure

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

K. Utilisation de l'option WITH ENCRYPTION

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

S’applique aux: SQL Server 2008 via SQL Server 2016, base de données SQL.
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  
  

La WITH ENCRYPTION option obscurcit la définition de la procédure lors de l’interrogation des catalogues système ou à l’aide des métadonnées des fonctions, comme indiqué dans les exemples suivants.

Run sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

Voici l'ensemble des résultats.

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

Interroger directement la sys.sql_modules affichage catalogue :

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

Voici l'ensemble des résultats.

definition

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

NULL

Forcer la recompilation de 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.

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

M. Utilisation de la clause EXECUTE AS

L’exemple suivant illustre l’utilisation du EXECUTE AS clause pour spécifier le contexte de sécurité dans lequel une procédure peut être exécutée. Dans l’exemple, l’option CALLER Spécifie 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  
  

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

O. Créer une procédure stockée qui exécute une instruction SELECT

Cet exemple montre la syntaxe de base pour la création et l’exécution d’une procédure. Lors de l’exécution d’un lot, CREATE PROCEDURE doit être la première instruction. Par exemple, pour créer la procédure stockée dans AdventureWorksPDW2012, définissez d’abord le contexte de base de données, puis exécutez l’instruction CREATE PROCEDURE.

-- Uses AdventureWorks  
  
--Run CREATE PROCEDURE as the first statement in a batch.  
CREATE PROCEDURE Get10TopResellers   
AS   
BEGIN  
    SELECT TOP (10) r.ResellerName, r.AnnualSales  
    FROM DimReseller AS r  
    ORDER BY AnnualSales DESC, ResellerName ASC;  
END  
;  
  
--Show 10 Top Resellers  
EXEC Get10TopResellers;  
  
--DROP the procedure if you need to re-run the example.  
DROP PROCEDURE Get10TopResellers;  
  

ALTER PROCEDURE (Transact-SQL)
Langage de contrôle de flux (Transact-SQL)
Curseurs
Types de données (Transact-SQL)
DÉCLARER @local_variable (Transact-SQL)
PROCÉDURE de suppression (Transact-SQL)
EXÉCUTER (Transact-SQL)
EXECUTE AS (Transact-SQL)
Procédures stockées (moteur de base de données)
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)
Créer une procédure stockée
Utiliser des paramètres table (moteur de base de données)
Sys.dm_sql_referenced_entities (Transact-SQL)
Sys.dm_sql_referencing_entities (Transact-SQL)

Ajouts de la communauté

AJOUTER
Afficher: