CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)

CETTE RUBRIQUE S’APPLIQUE À :ouiSQL Server (à partir de la version 2008)ouiAzure SQL DatabaseouiAzure SQL Data Warehouse ouiParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Crée un Transact-SQLTransact-SQL ou common language runtime (CLR) procédure stockée dans SQL ServerSQL Server, Azure SQL DatabaseAzure SQL Database, Azure SQL Data Warehouse et Parallel Data Warehouse.Creates a Transact-SQLTransact-SQL or common language runtime (CLR) stored procedure in SQL ServerSQL Server, Azure SQL DatabaseAzure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. Les procédures stockées ressemblent aux procédures d'autres langages de programmation, car elles peuvent :Stored procedures are similar to procedures in other programming languages in that they can:

  • 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 ;Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

  • 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 ;Contain programming statements that perform operations in the database, including calling other procedures.

  • 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).Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

    Utilisez cette instruction pour créer une procédure permanente dans la base de données actuelle ou une procédure temporaire dans le tempdb base de données.Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.

Note

L’intégration du CLR .NET Framework dans SQL Server est décrite dans cette rubrique.The integration of .NET Framework CLR into SQL Server is discussed in this topic. Intégration du CLR ne s’applique pas à Azure Base de données SQLSQL Database.CLR integration does not apply to Azure Base de données SQLSQL Database.

Atteindre exemples simples pour ignorer les détails de la syntaxe et obtenir un exemple de base de procédure stockée.Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

-- 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 ] }  
[;]  

ArgumentsArguments

OU ALTEROR ALTER
S’applique aux: Azure Base de données SQLSQL Database, SQL ServerSQL Server (en commençant par SQL Server 2016SQL Server 2016 SP1).Applies to: Azure Base de données SQLSQL Database, SQL ServerSQL Server (starting with SQL Server 2016SQL Server 2016 SP1).

Modifie la procédure s’il existe déjà.Alters the procedure if it already exists.

schema_nameschema_name
Le nom du schéma auquel appartient la procédure.The name of the schema to which the procedure belongs. Les procédures sont liées à un schéma.Procedures are schema-bound. 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é.If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

nom_procédureprocedure_name
Le nom de la procédure.The name of the procedure. Les noms des procédures doivent respecter les règles de identificateurs et doit être unique au sein du schéma.Procedure names must comply with the rules for identifiers and must be unique within the schema.

Évitez d’utiliser le sp_ de préfixe lorsque vous nommez des procédures.Avoid the use of the sp_ prefix when naming procedures. En effet, ce préfixe est utilisé par SQL ServerSQL Server pour faire référence aux procédures système.This prefix is used by SQL ServerSQL Server to designate system procedures. 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.Using the prefix can cause application code to break if there is a system procedure with the same name.

Les procédures temporaires locales ou globales peuvent être créés à l’aide d’un dièse (#) avant 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).Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). 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.A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. 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.A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. Des noms temporaires ne peuvent pas être indiqués pour les procédures CLR.Temporary names cannot be specified for CLR procedures.

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.The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. Le nom complet d'une procédure temporaire locale, y compris le signe #, ne peut dépasser 116 caractères.The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

; nombre; number
S’applique aux: SQL Server 2008SQL Server 2008 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

Entier facultatif qui permet de regrouper les procédures de même nom.An optional integer that is used to group procedures of the same name. Ces procédures groupées peuvent être supprimées en même temps par le biais d'une seule instruction DROP PROCEDURE.These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

Note

Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server.This feature will be removed in a future version of 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é.Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Procédures numérotées ne peut pas utiliser le xml ou CLR défini par l’utilisateur, types et ne peut pas être utilisé dans un repère de plan.Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

@paramètre@ parameter
Paramètre déclaré dans la procédure.A parameter declared in the procedure. Spécifiez un nom de paramètre à l’aide de l’arobase (@) comme premier caractère.Specify a parameter name by using the at sign (@) as the first character. Le nom du paramètre doit respecter les règles de identificateurs.The parameter name must comply with the rules for identifiers. Un paramètre étant local à une procédure, vous pouvez utiliser le même nom dans d'autres procédures.Parameters are local to the procedure; the same parameter names can be used in other procedures.

Un ou plusieurs paramètres peuvent être déclarés, dans la limite de 2 100.One or more parameters can be declared; the maximum is 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.The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. Si une procédure contient paramètres tableet que le paramètre est manquant dans l’appel, une table vide est passée.If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. 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.Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. Pour plus d’informations, consultez EXECUTE (Transact-SQL).For more information, see EXECUTE (Transact-SQL).

Il n'est pas possible de déclarer des paramètres si FOR REPLICATION est spécifié.Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name.[ type_schema_name. ] data_type] data_type
Type de données du paramètre et du schéma auquel le type de données appartient.The data type of the parameter and the schema to which the data type belongs.

Recommandations pour Transact-SQLTransact-SQL procédures:Guidelines for Transact-SQLTransact-SQL procedures:

  • Tous les Transact-SQLTransact-SQL les types de données peuvent être utilisés en tant que paramètres.All Transact-SQLTransact-SQL data types can be used as parameters.

  • Vous pouvez utiliser le type de table défini par l'utilisateur pour créer des paramètres table.You can use the user-defined table type to create table-valued parameters. 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.Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. Pour plus d’informations, consultez utiliser des paramètres table ( moteur de base de données )For more information, see Use Table-Valued Parameters (Database Engine)

  • curseur types de données ne peut être que des paramètres de sortie et doivent être accompagnés du mot clé VARYING.cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

Recommandations pour les procédures CLR:Guidelines for CLR procedures:

  • Tous les types de données SQL ServerSQL Server natifs qui ont un équivalent en code managé peuvent être utilisés en tant que paramètres.All of the native SQL ServerSQL Server data types that have an equivalent in managed code can be used as parameters. Pour plus d’informations sur la correspondance entre les types CLR et SQL ServerSQL Server les types de données système, consultez de mappage de données de paramètre CLR.For more information about the correspondence between CLR types and SQL ServerSQL Server system data types, see Mapping CLR Parameter Data. Pour plus d’informations sur SQL ServerSQL Server types de données système et leur syntaxe, consultez Types de données ( Transact-SQL ) .For more information about SQL ServerSQL Server system data types and their syntax, see Data Types (Transact-SQL).

  • Table-valued ou curseur des types de données ne peut pas être utilisés en tant que paramètres.Table-valued or cursor data types cannot be used as parameters.

  • 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.If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

VARYINGVARYING
Spécifie le jeu de résultats pris en charge comme paramètre de sortie.Specifies the result set supported as an output parameter. Ce paramètre est construit dynamiquement par la procédure ; il se peut donc que son contenu varie.This parameter is dynamically constructed by the procedure and its contents may vary. S’applique uniquement aux curseur paramètres.Applies only to cursor parameters. Cette option n'est pas valide pour les procédures CLR.This option is not valid for CLR procedures.

par défautdefault
Valeur par défaut pour un paramètre.A default value for a parameter. Si la 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.If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. La valeur par défaut doit être une constante ou il peut s'agir de la valeur NULL.The default value must be a constant or it can be 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.The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

Valeurs par défaut sont enregistrés dans le sys.parameters.default colonne uniquement pour les procédures CLR.Default values are recorded in the sys.parameters.default column only for CLR procedures. Cette colonne est NULL pour Transact-SQLTransact-SQL les paramètres de procédure.That column is NULL for Transact-SQLTransact-SQL procedure parameters.

OUT | OUTPUTOUT | OUTPUT
Indique que le paramètre est un paramètre de sortie.Indicates that the parameter is an output parameter. Utilisez les paramètres OUTPUT pour retourner les valeurs à la procédure appelante.Use OUTPUT parameters to return values to the caller of the procedure. 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.text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. 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).An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. Un type de données table ne peut pas être spécifié comme paramètre OUTPUT d'une procédure.A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

READONLYREADONLY
Indique que le paramètre ne peut pas être mis à jour ou modifié dans le corps de la procédure.Indicates that the parameter cannot be updated or modified within the body of the procedure. Si le type de paramètre est un type de table, READONLY doit être spécifié.If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
Indique que le Moteur de base de donnéesDatabase Engine 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.Indicates that the Moteur de base de donnéesDatabase Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. Pour plus d’informations sur les raisons d’une recompilation forcée, consultez recompiler une procédure stockée.For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. Cette option ne peut pas être utilisée lorsque FOR REPLICATION est spécifié ou pour les procédures CLR (Common Language Runtime).This option cannot be used when FOR REPLICATION is specified or for CLR procedures.

Pour indiquer à le Moteur de base de donnéesDatabase Engine 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.To instruct the Moteur de base de donnéesDatabase Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. Pour plus d’informations, consultez Indicateurs de requête (Transact-SQL).For more information, see Query Hints (Transact-SQL).

ENCRYPTIONENCRYPTION
S’applique aux: SQL Server ( SQL Server 2008SQL Server 2008 via SQL Server 2017SQL Server 2017), Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL DatabaseAzure SQL Database.

Indique que SQL ServerSQL Server convertit le texte d’origine de l’instruction CREATE PROCEDURE dans un format obscurci.Indicates that SQL ServerSQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. La sortie générée par l'obfuscation n'est pas visible directement dans les affichages catalogue de SQL ServerSQL Server.The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL 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.Users who have no access to system tables or database files cannot retrieve the obfuscated text. Toutefois, le texte est disponible pour les utilisateurs disposant de privilèges qui peuvent accéder soit les tables système via la port DAC ou accéder directement aux fichiers de base de données.However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. 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.Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. Pour plus d’informations sur l’accès aux métadonnées système, consultez Metadata Visibility Configuration.For more information about accessing system metadata, see Metadata Visibility Configuration.

Cette option n'est pas valide pour les procédures CLR.This option is not valid for CLR procedures.

Les procédures créées à l'aide de cette option ne peuvent pas être publiées dans le cadre d'une réplication SQL ServerSQL Server.Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

EXECUTE AS clauseEXECUTE AS clause
Indique le contexte de sécurité dans lequel la procédure doit être exécutée.Specifies the security context under which to execute the procedure.

Pour les procédures stockées compilées en mode natif, démarrage SQL Server 2016SQL Server 2016 et dans Azure SQL DatabaseAzure SQL Database, il n’existe aucune limitation sur EXECUTE AS clause.For natively compiled stored procedures, starting SQL Server 2016SQL Server 2016 and in Azure SQL DatabaseAzure SQL Database, there are no limitations on the EXECUTE AS clause. Dans SQL Server 2014SQL Server 2014 le SELF, OWNER et 'nom_utilisateur' clauses sont pris en charge avec des procédures stockées compilées en mode natif.In SQL Server 2014SQL Server 2014 the SELF, OWNER, and ‘user_name’ clauses are supported with natively compiled stored procedures.

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

FOR REPLICATIONFOR REPLICATION
S’applique aux: SQL Server ( SQL Server 2008SQL Server 2008 via SQL Server 2017SQL Server 2017), Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL DatabaseAzure SQL Database.

Spécifie que la procédure est créée en vue d'une réplication.Specifies that the procedure is created for replication. Par conséquent, elle ne peut pas être exécutée sur l'Abonné.Consequently, it cannot be executed on the Subscriber. 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.A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. Il n'est pas possible de déclarer des paramètres si FOR REPLICATION est spécifié.Parameters cannot be declared if FOR REPLICATION is specified. FOR REPLICATION ne peut pas être précisé pour une utilisation avec des procédures CLR.FOR REPLICATION cannot be specified for CLR procedures. L'option RECOMPILE est ignorée pour les procédures créées avec l'option FOR REPLICATION.The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

A FOR REPLICATION procédure a un type d’objet RF dans sys.objects et sys.procedures.A FOR REPLICATION procedure has an object type RF in sys.objects and sys.procedures.

{[BEGIN] sql_statement [ ;] [ ... n ] [END]}{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Une ou plusieurs instructions Transact-SQLTransact-SQL comprenant le corps de la procédure.One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. Vous pouvez utiliser les mots clés facultatifs BEGIN et END pour délimiter les instructions.You can use the optional BEGIN and END keywords to enclose the statements. Pour plus d'informations, consultez les sections suivantes intitulées Meilleures pratiques, Remarques d'ordre général et Limitations et restrictions.For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

NOM externe assembly_name. class_name. nom_méthodeEXTERNAL NAME assembly_name.class_name.method_name
S’applique aux: SQL Server 2008SQL Server 2008 via SQL Server 2017SQL Server 2017, Base de données SQLSQL Database.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, Base de données SQLSQL Database.

Spécifie la méthode d'un assembly .NET Framework.NET Framework pour une procédure CLR à référencer.Specifies the method of a .NET Framework.NET Framework assembly for a CLR procedure to reference. CLASS_NAME doit être valide SQL ServerSQL Server identificateur et doit exister en tant que classe dans l’assembly.class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the 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 (» «).If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). La méthode spécifiée doit être une méthode statique de la classe.The specified method must be a static method of the class.

Par défaut, SQL ServerSQL Server ne peut pas exécuter du code CLR.By default, SQL ServerSQL Server cannot execute CLR code. Vous pouvez créer, modifier et supprimer des objets de base de données qui référencent des modules de runtime de langage commun ; Toutefois, vous ne pouvez pas exécuter ces références dans SQL ServerSQL Server jusqu'à ce que vous activiez la option clr enabled.You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. Pour activer l’option, utilisez sp_configure.To enable the option, use sp_configure.

Note

Les procédures CLR ne sont pas prises en charge dans une base de données à relation contenant-contenu.CLR procedures are not supported in a contained database.

ATOMIC WITHATOMIC WITH
S’applique aux: SQL Server 2014SQL Server 2014 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

Indique l'exécution automatique d'une procédure stockée Atomic.Indicates atomic stored procedure execution. Les modifications sont validées ou bien tous les changements sont restaurés en levant une exception.Changes are either committed or all of the changes rolled back by throwing an exception. Le bloc ATOMIC WITH est requis pour les procédures stockées compilées en mode natif.The ATOMIC WITH block is required for natively compiled stored procedures.

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é.If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. Si la procédure lève une exception, le travail effectué par la procédure est restauré.If the procedure THROWs, the work performed by the procedure is rolled back.

XACT_ABORT est activé (ON) par défaut dans un bloc Atomic et ne peut pas être modifié.XACT_ABORT is ON by default inside an atomic block and cannot be changed. XACT_ABORT indique si SQL ServerSQL Server restaure automatiquement la transaction en cours lorsqu'une instruction Transact-SQLTransact-SQL déclenche une erreur d'exécution.XACT_ABORT specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

Les options SET suivantes sont toujours activées (ON) dans le bloc ATOMIC ; les options ne peuvent pas être modifiées.The following SET options are always ON in the ATOMIC block; the options cannot be changed.

  • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER, ARITHABORTQUOTED_IDENTIFIER, ARITHABORT
  • NOCOUNTNOCOUNT
  • ANSI_NULLSANSI_NULLS
  • ANSI_WARNINGSANSI_WARNINGS

Les options SET ne peuvent pas être modifiées dans les blocs ATOMIC.SET options cannot be changed inside ATOMIC blocks. 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.The SET options in the user session are not used in the scope of natively compiled stored procedures. Ces options sont résolues au moment de la compilation.These options are fixed at compile time.

Les opérations BEGIN, ROLLBACK et COMMIT ne peuvent pas être utilisées dans un bloc Atomic.BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

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.There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. Les blocs ne peuvent pas être imbriqués.The blocks cannot be nested. Pour plus d’informations sur les blocs atomic, consultez Natively Compiled Stored Procedures.For more information about atomic blocks, see Natively Compiled Stored Procedures.

NULL | NON NULLNULL | NOT NULL
Détermine si les valeurs Null sont autorisées dans un paramètre.Determines whether null values are allowed in a parameter. NULL est l'argument par défaut.NULL is the default.

NATIVE_COMPILATIONNATIVE_COMPILATION
S’applique aux: SQL Server 2014SQL Server 2014 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

Indique que la procédure est compilée en mode natif.Indicates that the procedure is natively compiled. NATIVE_COMPILATION, SCHEMABINDING et EXECUTE AS peuvent être spécifiés dans n'importe quel ordre.NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. Pour plus d’informations, consultez Natively Compiled Stored Procedures.For more information, see Natively Compiled Stored Procedures.

SCHEMABINDINGSCHEMABINDING
S’applique aux: SQL Server 2014SQL Server 2014 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

Garantit que les tables référencées par une procédure ne peuvent pas être supprimées ou modifiées.Ensures that tables that are referenced by a procedure cannot be dropped or altered. SCHEMABINDING est requis dans les procédures stockées compilées en mode natif.SCHEMABINDING is required in natively compiled stored procedures. (Pour plus d’informations, consultez Natively Compiled Stored Procedures.) Les restrictions SCHEMABINDING sont les mêmes que pour les fonctions définies par l'utilisateur.(For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. Pour plus d’informations, consultez la section SCHEMABINDING dans CREATE FUNCTION ( Transact-SQL ) .For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'langue'LANGUAGE = [N] 'language'
S’applique aux: SQL Server 2014SQL Server 2014 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

Équivalent à définir LANGUAGE ( Transact-SQL ) option de session.Equivalent to SET LANGUAGE (Transact-SQL) session option. LANGUAGE = [N] 'language' est requis.LANGUAGE = [N] 'language' is required.

TRANSACTION ISOLATION LEVELTRANSACTION ISOLATION LEVEL
S’applique aux: SQL Server 2014SQL Server 2014 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

Requis pour les procédures stockées compilées en mode natif.Required for natively compiled stored procedures. Spécifie le niveau d'isolation de la transaction pour la procédure stockée.Specifies the transaction isolation level for the stored procedure. Les options disponibles sont les suivantes :The options are as follows:

Pour plus d’informations sur ces options, consultez SET TRANSACTION ISOLATION LEVEL ( Transact-SQL ) .For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READREPEATABLE READ
Spécifie que les instructions ne peuvent pas lire des données modifiées et non encore validées par d'autres transactions.Specifies that statements cannot read data that has been modified but not yet committed by other transactions. Si une autre transaction modifie des données qui a été lu par la transaction actuelle, la transaction en cours échoue.If another transaction modifies data that has been read by the current transaction, the current transaction fails.

SERIALIZABLESERIALIZABLE
Spécifie les indications suivantes :Specifies the following:

  • Les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions.Statements cannot read data that has been modified but not yet committed by other transactions.
  • Si une autre transaction modifie des données qui a été lu par la transaction actuelle, la transaction en cours échoue.If another transactions modifies data that has been read by the current transaction, the current transaction fails.
  • Si une autre transaction insère de nouvelles lignes avec des valeurs de clés comprises dans la plage de clés lues par toutes les instructions dans la transaction en cours, la transaction en cours échoue.If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.

SNAPSHOTSNAPSHOT
Spécifie que les données lues par n’importe quelle instruction dans une transaction sont la version cohérente des données qui existaient au début de la transaction.Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.

DATEFIRST = nombreDATEFIRST = number
S’applique aux: SQL Server 2014SQL Server 2014 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

Spécifie le premier jour de la semaine pour un nombre compris entre 1 et 7.Specifies the first day of the week to a number from 1 through 7. DATEFIRST est facultatif.DATEFIRST is optional. Si cela n'est pas spécifié, le paramètre est déduit en fonction du langage spécifié.If it is not specified, the setting is inferred from the specified language.

Pour plus d’informations, consultez SET DATEFIRST ( Transact-SQL ) .For more information, see SET DATEFIRST (Transact-SQL).

DATEFORMAT = formatDATEFORMAT = format
S’applique aux: SQL Server 2014SQL Server 2014 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

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.Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. DATEFORMAT est facultatif.DATEFORMAT is optional. Si cela n'est pas spécifié, le paramètre est déduit en fonction du langage spécifié.If it is not specified, the setting is inferred from the specified language.

Pour plus d’informations, consultez SET DATEFORMAT ( Transact-SQL ) .For more information, see SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }DELAYED_DURABILITY = { OFF | ON }
S’applique aux: SQL Server 2014SQL Server 2014 via SQL Server 2017SQL Server 2017 et Azure SQL DatabaseAzure SQL Database.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

Les validations de transactions SQL ServerSQL Server peuvent avoir une durabilité complète, la durabilité par défaut ou une durabilité retardée. SQL ServerSQL Server transaction commits can be either fully durable, the default, or delayed durable.

Pour plus d’informations, consultez contrôle la durabilité des transactions.For more information, see Control Transaction Durability.

Exemples simplesSimple Examples

Pour vous aider à démarrer, voici deux exemples :To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB;Retourne le nom de la base de données actuelle.SELECT DB_NAME() AS ThisDB; returns the name of the current database.
Vous pouvez encapsuler cette instruction dans une procédure stockée, telles que :You can wrap that statement in a stored procedure, such as:

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;Call the store procedure with statement: EXEC What_DB_is_this;

Il est légèrement plus complexe, pour fournir un paramètre d’entrée pour rendre la procédure plus flexible.Slightly more complex, is to provide an input parameter to make the procedure more flexible. Exemple :For example:

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.Provide a database id number when you call the procedure. Par exemple, EXEC What_DB_is_that 2; retourne tempdb.For example, EXEC What_DB_is_that 2; returns tempdb.

Consultez exemples vers la fin de cette rubrique pour obtenir des exemples plus nombreux.See Examples towards the end of this topic for many more examples.

Bonnes pratiquesBest Practices

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.Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • Utilisez l'instruction SET NOCOUNT ON comme première instruction dans le corps de la procédure.Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. Autrement dit, placez-la juste après le mot clé AS.That is, place it just after the AS keyword. Cela permet de désactiver les messages renvoyés par SQL ServerSQL Server au client une fois les instructions SELECT, INSERT, UPDATE, MERGE et DELETE exécutées.This turns off messages that SQL ServerSQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. 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.Overall performance of the database and application is improved by eliminating this unnecessary network overhead. Pour plus d’informations, consultez SET NOCOUNT ( Transact-SQL ) .For information, see 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.Use schema names when creating or referencing database objects in the procedure. Il prend moins de temps le Moteur de base de donnéesDatabase Engine pour résoudre les noms de l’objet si elle n’a pas à rechercher dans plusieurs schémas.It takes less processing time for the Moteur de base de donnéesDatabase Engine to resolve object names if it does not have to search multiple schemas. Elle évite également d’autorisation et les problèmes d’accès de schéma par défaut de l’utilisateur qui est affecté lors de la création d’objets sans spécifier le schéma.It also prevents permission and access problems caused by a user’s default schema being assigned when objects are created without specifying the schema.

  • Évitez les fonctions de renvoi à la ligne pour les fonctions autour des colonnes spécifiées dans les clauses WHERE et JOIN.Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Les colonnes seront ainsi non déterministes, ce qui empêche le processeur de requêtes d'utiliser des index.Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • Évitez d'utiliser des fonctions scalaires dans des instructions SELECT qui retournent un grand nombre de lignes de données.Avoid using scalar functions in SELECT statements that return many rows of data. Étant donné que la fonction scalaire doit être appliquée à chaque ligne, le comportement s'apparente à un traitement par ligne et nuit aux performances.Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • Évitez d’utiliser SELECT *.Avoid the use of SELECT *. utilisez les noms de colonnes requisInstead, specify the required column names. afin d'éviter que des erreurs du Moteur de base de donnéesDatabase Engine n'interrompent l'exécution de la procédure.This can prevent some Moteur de base de donnéesDatabase Engine errors that stop procedure execution. Par exemple, un SELECT * instruction qui retourne des données à partir d’une table contenant 12 colonnes, puis insère ces données dans une table temporaire de 12 colonnes réussit tant que le nombre ou l’ordre des colonnes dans une table est modifiée.For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.

  • Évitez de traiter ou de retourner un trop grand nombre de données.Avoid processing or returning too much data. 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.Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Envoyez uniquement les données essentielles à l'application cliente.Send just the essential data to the client application. 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.It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • Utiliser des transactions explicites à l’aide de BEGIN/COMMIT TRANSACTION et réduisez autant que possible les transactions.Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. Les transactions plus longues entraînent un verrouillage plus long des enregistrements et un plus grand risque de blocage.Longer transactions mean longer record locking and a greater potential for deadlocking.

  • Utilisez la fonction Transact-SQLTransact-SQL TRY…CATCH pour la gestion des erreurs au sein d'une procédure.Use the Transact-SQLTransact-SQL TRY…CATCH feature for error handling inside a procedure. TRY…CATCH peut encapsuler un bloc entier d'instructions Transact-SQLTransact-SQL.TRY…CATCH can encapsulate an entire block of Transact-SQLTransact-SQL statements. 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.This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.

  • Utilisez le mot clé DEFAULT sur toutes les colonnes de table qui sont référencées par des instructions Transact-SQLTransact-SQL CREATE TABLE ou ALTER TABLE dans le corps de la procédure.Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQLTransact-SQL statements in the body of the procedure. Cela empêche le passage de NULL pour les colonnes qui n’autorisent pas les valeurs null.This prevents passing NULL to columns that do not allow null values.

  • Utilisez la valeur NULL ou NOT NULL pour chaque colonne d'une table temporaire.Use NULL or NOT NULL for each column in a temporary table. Les options ANSI_DFLT_ON et ANSI_DFLT_OFF définissent la manière dont le Moteur de base de donnéesDatabase Engine 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.The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Moteur de base de donnéesDatabase Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. 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.If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. 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.If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • Utilisez des instructions de modification qui convertissent les valeurs Null et incluez une logique éliminant des requêtes les lignes contenant des valeurs Null.Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Gardez à l’esprit que, dans Transact-SQLTransact-SQL, NULL n’est pas vide, ou la valeur « nothing ».Be aware that in Transact-SQLTransact-SQL, NULL is not an empty or "nothing" value. 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.It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • Utilisez l'opérateur UNION ALL au lieu des opérateurs UNION ou OR, sauf si vous avez besoin de valeurs distinctes.Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. 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.The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

Remarques d'ordre généralGeneral Remarks

Il n'existe pas de taille maximale prédéfinie pour une procédure.There is no predefined maximum size of a procedure.

Les variables spécifiées dans la procédure peuvent être défini par l’utilisateur ou des variables système, telles que @@SPID.Variables specified in the procedure can be user-defined or system variables, such as @@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.When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. 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éesDatabase Engine.Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the Moteur de base de donnéesDatabase Engine.

Il est possible de lancer l'exécution automatique d'une ou plusieurs procédures au démarrage de SQL ServerSQL Server.One or more procedures can execute automatically when SQL ServerSQL Server starts. Les procédures doivent être créées par l’administrateur système dans le master de base de données et exécutées sous le sysadmin rôle serveur fixe comme un processus en arrière-plan.The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. Les procédures ne peuvent pas comprendre de paramètres d'entrée ou de sortie.The procedures cannot have any input or output parameters. Pour plus d’informations, consultez exécuter une procédure stockée.For more information, see Execute a Stored Procedure.

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.Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. Vous pouvez imbriquer des procédures et des références au code managé jusqu'à 32 niveaux.Procedures and managed code references can be nested up to 32 levels. 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.The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. Les méthodes appelées à partir du code managé n'entrent pas en compte dans la limite de niveau d'imbrication.Methods invoked from within the managed code do not count against the nesting level limit. 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.However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.

Au-delà du niveau d'imbrication maximal, toute la chaîne d'appels échoue.Attempting to exceed the maximum nesting level causes the entire calling chain to fail. Vous pouvez utiliser le @@NESTLEVEL fonction pour renvoyer le niveau d’imbrication de l’exécution de procédure stockée actuelle.You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

InteropérabilitéInteroperability

Le Moteur de base de donnéesDatabase Engine 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-SQLTransact-SQL.The Moteur de base de donnéesDatabase Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQLTransact-SQL procedure is created or modified. Ces paramètres d'origine sont utilisés lors de l'exécution de la procédure.These original settings are used when the procedure is executed. 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.Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

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.Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. 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.If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. 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.When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. Les paramètres reprennent ensuite la valeur qu'ils avaient avant l'appel de la procédure.The setting is then restored to the value the procedure had when it was called. Ceci permet aux clients individuels de définir les options souhaitées sans affecter la logique de la procédure.This enables individual clients to set the options they want without affecting the logic of the procedure.

Toute instruction SET peut être indiquée dans une procédure, sauf SET SHOWPLAN_TEXT et SET SHOWPLAN_ALL.Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. Elles doivent être les seules instructions d'un lot.These must be the only statements in the batch. L'option SET choisie reste en vigueur durant l'exécution de la procédure, puis retrouve sa valeur d'origine.The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

Note

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.SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. 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.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Limitations et restrictionsLimitations and Restrictions

L'instruction CREATE PROCEDURE ne peut pas s'utiliser conjointement avec d'autres instructions Transact-SQLTransact-SQL dans un même lot.The CREATE PROCEDURE statement cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

Les instructions suivantes ne peuvent pas être utilisées dans le corps d'une procédure stockée.The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATECREATE AGGREGATE CREATE SCHEMACREATE SCHEMA SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT
CREATE DEFAULTCREATE DEFAULT CREATE ou ALTER TRIGGERCREATE or ALTER TRIGGER SET SHOWPLAN_XMLSET SHOWPLAN_XML
CREATE ou ALTER FUNCTIONCREATE or ALTER FUNCTION CREATE ou ALTER VIEWCREATE or ALTER VIEW Utilisez nom_base_de_donnéesUSE database_name
CREATE ou ALTER PROCEDURECREATE or ALTER PROCEDURE SET PARSEONLYSET PARSEONLY
CREATE RULECREATE RULE SET SHOWPLAN_ALLSET SHOWPLAN_ALL

Une procédure peut faire référence à des tables qui n'existent pas encore.A procedure can reference tables that do not yet exist. Au moment de la création, seul le contrôle de la syntaxe est effectué.At creation time, only syntax checking is performed. La procédure n'est compilée qu'à sa première exécution.The procedure is not compiled until it is executed for the first time. Ce n'est qu'au moment de la compilation que la procédure résout les références aux objets.Only during compilation are all objects referenced in the procedure resolved. Par conséquent, une procédure syntaxiquement correcte qui fait référence à des tables qui n’existent pas peut être créée avec succès ; Toutefois, la procédure échoue au moment de l’exécution si les tables référencées n’existent pas.Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

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.You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. En revanche, vous pouvez passer une fonction comme variable, comme indiqué dans l'exemple suivant.However, you can pass a function as a variable as shown in the following example.

-- 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 ServerSQL Server, les modifications ne peuvent pas être restaurées.If the procedure makes changes on a remote instance of SQL ServerSQL Server, the changes cannot be rolled back. Les procédures distantes ne font pas partie des transactions.Remote procedures do not take part in transactions.

Pour que le Moteur de base de donnéesDatabase Engine 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 :For the Moteur de base de donnéesDatabase Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:

  • elle doit être déclarée en tant que méthode statique ;Be declared as a static method.

  • elle doit compter le même nombre de paramètres que la procédure ;Receive the same number of parameters as the number of parameters of the procedure.

  • les types de paramètres utilisés doivent être compatibles avec ceux des paramètres correspondant de la procédure SQL ServerSQL Server.Use parameter types that are compatible with the data types of the corresponding parameters of the SQL ServerSQL Server procedure. Pour plus d’informations sur la correspondance SQL ServerSQL Server des types de données pour le .NET Framework.NET Framework des types de données, consultez de mappage de données de paramètre CLR.For information about matching SQL ServerSQL Server data types to the .NET Framework.NET Framework data types, see Mapping CLR Parameter Data.

MétadonnéesMetadata

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.The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.

AffichageView DescriptionDescription
sys.sql_modulessys.sql_modules Retourne la définition d'une procédure Transact-SQLTransact-SQL.Returns the definition of a Transact-SQLTransact-SQL procedure. 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.The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
sys.assembly_modulessys.assembly_modules Retourne des informations sur une procédure CLR.Returns information about a CLR procedure.
sys.parameterssys.parameters Retourne des informations sur les paramètres définis dans une procédure.Returns information about the parameters that are defined in a procedure
Sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entitiessys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities Retourne les objets référencés par une procédure.Returns the objects that are referenced by a procedure.

Pour estimer la taille d'une procédure compilée, utilisez les compteurs de l'Analyseur de performances décrits ci-dessous.To estimate the size of a compiled procedure, use the following Performance Monitor Counters.

Nom de l'objet de l'Analyseur de performancesPerformance Monitor object name Nom du compteur de l'Analyseur de performancesPerformance Monitor Counter name
SQLServer : Objet Plan CacheSQLServer: Plan Cache Object Taux d'accès au cacheCache Hit Ratio
Pages du cacheCache Pages
Nombre d'objets cacheCache Object Counts

* Ces compteurs sont disponibles pour diverses catégories d'objets du cache, y compris Transact-SQLTransact-SQL ad hoc, Transact-SQLTransact-SQL préparé, procédures, déclencheurs, etc.*These counters are available for various categories of cache objects including ad hoc Transact-SQLTransact-SQL, prepared Transact-SQLTransact-SQL, procedures, triggers, and so on. Pour plus d’informations, consultez SQL Server, objet du Cache de Plan.For more information, see SQL Server, Plan Cache Object.

SécuritéSecurity

PermissionsPermissions

Requiert CREATE PROCEDURE autorisation dans la base de données et ALTER autorisation sur le schéma dans lequel la procédure est créée, ou nécessite l’appartenance au db_ddladmin rôle de base de données fixe.Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

Les 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.For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES permission on that assembly.

CREATE PROCEDURE et Tables optimisées en mémoireCREATE PROCEDURE and Memory-Optimized Tables

Tables optimisées en mémoire est accessible via des procédures stockées compilées en mode natif et traditionnelles.Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. Procédures natives sont, dans la plupart des cas, la manière la plus efficace.Native procedures are in most cases the more efficient way. Pour plus d’informations, consultez Natively Compiled Stored Procedures.For more information, see Natively Compiled Stored Procedures.

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:The following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table 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.A procedure created without NATIVE_COMPILATION cannot be altered to a natively compiled stored procedure.

Pour en savoir plus 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 voir des opérateurs fonctionnalités prises en charge pour la compilation en mode natif les Modules T-SQL.For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.

ExemplesExamples

CatégorieCategory Éléments syntaxiques proposésFeatured syntax elements
Syntaxe de baseBasic Syntax CREATE PROCEDURECREATE PROCEDURE
Passage de paramètresPassing parameters @parameter
  • = par défaut   • = default
  • SORTIE   • OUTPUT
  type de paramètre table •   • table-valued parameter type
  • CURSOR VARYING   • CURSOR VARYING
Modification des données à l’aide d’une procédure stockéeModifying data by using a stored procedure UPDATEUPDATE
Gestion des erreursError Handling TRY…CATCHTRY…CATCH
Masquage de la définition de procédureObfuscating the procedure definition WITH ENCRYPTIONWITH ENCRYPTION
Forcer la recompilation de procédureForcing the Procedure to Recompile WITH RECOMPILEWITH RECOMPILE
Définition du contexte de sécuritéSetting the Security Context EXECUTE ASEXECUTE AS

Syntaxe de baseBasic Syntax

Les exemples fournis dans cette section présentent les fonctionnalités de base de l'instruction CREATE PROCEDURE en utilisant la syntaxe minimale requise.Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.

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

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 AdventureWorks2012AdventureWorks2012.The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2012AdventureWorks2012 database. Cette procédure n'utilise aucun paramètre.This procedure does not use any parameters. L'exemple illustre ensuite trois méthodes permettant d'exécuter la procédure.The example then demonstrates three methods of executing the procedure.

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 :The uspGetEmployees procedure can be executed in the following ways:

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

B.B. Renvoi de plusieurs jeux de résultatsReturning more than one result set

La procédure suivante renvoie deux jeux de résultats.The following procedure returns two result sets.

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

C.C. Création d'une procédure stockée CLRCreating a CLR stored procedure

L’exemple suivant crée la GetPhotoFromDB procédure qui fait référence à la GetPhotoFromDB méthode de la LargeObjectBinary classe dans le HandlingLOBUsingCLR assembly.The following example creates the GetPhotoFromDB procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. Avant que la procédure est créée, le HandlingLOBUsingCLR assembly est inscrit dans la base de données locale.Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

S’applique aux: SQL Server 2008SQL Server 2008 via SQL Server 2017SQL Server 2017, Base de données SQLSQL Database (si vous utilisez un assembly créé à partir de assembly_bits.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, Base de données SQLSQL Database (if using an assembly created from 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ètresPassing Parameters

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.Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

D.D. Création d'une procédure avec des paramètres d'entréeCreating a procedure with input parameters

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é.The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. Cette procédure accepte uniquement les correspondances exactes pour les paramètres passés.This procedure accepts only exact matches for the parameters passed.

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 :The uspGetEmployees procedure can be executed in the following ways:

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.E. Utilisation d'une procédure avec des paramètres génériquesUsing a procedure with wildcard parameters

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é.The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. Ce modèle de procédure fait correspondre les paramètres passés ou, si ne pas fourni, la valeur par défaut prédéfinie (dont le nom commence par la lettre D).This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter 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 dans un nombre de combinaisons.The uspGetEmployees2 procedure can be executed in many combinations. Vous trouverez ci-dessous certaines des combinaisons possibles.Only a few possible combinations are shown here.

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.F. Utilisation des paramètres OUTPUTUsing OUTPUT parameters

L'exemple suivant crée la procédure uspGetList.The following example creates the uspGetList procedure. Cette procédure retourne une liste de produits dont le prix ne dépasse pas un montant précisé.This procedures returns a list of products that have prices that do not exceed a specified amount. L'exemple illustre l'utilisation de plusieurs instructions SELECT et de plusieurs paramètres OUTPUT.The example shows using multiple SELECT statements and multiple OUTPUT parameters. Les paramètres OUTPUT permettent à une procédure externe, un lot ou à plus d'une instruction Transact-SQLTransact-SQL d'accéder à un ensemble de valeurs pendant l'exécution de la procédure.OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

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 WorksAdventure Works et coûtant moins de $700.Execute uspGetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $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.The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.

Note

La variable OUTPUT doit être définie lors de la création de la procédure et de l'utilisation de la variable.The OUTPUT variable must be defined when the procedure is created and also when the variable is used. Le nom du paramètre et le nom de la variable n’ont pas à correspondre ; Toutefois, le type de données et la position du paramètre doivent correspondre à, sauf si @ListPrice = variable est utilisé.The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice = variable is used.

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 :Here is the partial result set:

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.G. Utilisation d'un paramètre tableUsing a Table-Valued Parameter

L'exemple suivant utilise un type de paramètre table pour insérer plusieurs lignes dans une table.The following example uses a table-valued parameter type to insert multiple rows into a 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.The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. La procédure stockée utilise les valeurs pour insérer plusieurs lignes dans une table.The stored procedure uses the values to insert multiple rows into a 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.H. Utilisation d'un paramètre OUTPUT de type cursorUsing an OUTPUT cursor parameter

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.The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.

Commencez par créer la procédure qui déclare un curseur puis l'ouvre dans la table Currency :First, create the procedure that declares and then opens a cursor on the Currency table:

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.Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

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éeModifying Data by using a Stored Procedure

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.Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

I.I. Utilisation de l'instruction UPDATE dans une procédure stockéeUsing UPDATE in a stored procedure

L'exemple ci-dessous utilise une instruction UPDATE dans une procédure stockée.The following example uses an UPDATE statement in a stored procedure. La procédure accepte un paramètre d'entrée, @NewHours et un paramètre de sortie @RowCount.The procedure takes one input parameter, @NewHours and one output parameter @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.The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. Le paramètre de sortie @RowCount est utilisé pour retourner le nombre de lignes affectées à une variable locale.The @RowCount output parameter is used to return the number of rows affected to a local variable. Une expression CASE est utilisée dans la clause SET pour déterminer de manière conditionnelle la valeur définie pour VacationHours.A CASE expression is used in the SET clause to conditionally determine the value that is set for 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.When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @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 erreursError Handling

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.Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

J.J. Utilisation de TRY…CATCHUsing 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.The following example using the TRY…CATCH construct to return error information caught during the execution of a stored procedure.

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édureObfuscating the Procedure Definition

Les exemples de cette section illustrent comment obscurcir la définition de la procédure stockée.Examples in this section show how to obfuscate the definition of the stored procedure.

K.K. Utilisation de l'option WITH ENCRYPTIONUsing the WITH ENCRYPTION option

L'exemple suivant crée la procédure HumanResources.uspEncryptThis.The following example creates the HumanResources.uspEncryptThis procedure.

S’applique aux: SQL Server 2008SQL Server 2008 via SQL Server 2017SQL Server 2017, base de données SQL.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL Database.

CREATE PROCEDURE HumanResources.uspEncryptThis  
WITH ENCRYPTION  
AS  
    SET NOCOUNT ON;  
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, 
        VacationHours, SickLeaveHours   
    FROM HumanResources.Employee;  
GO  

Le 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.The WITH ENCRYPTION option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.

Run sp_helptext:Run sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

Voici l'ensemble des résultats.Here is the result set.

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

Interroger directement la sys.sql_modules affichage catalogue :Directly query the sys.sql_modules catalog view:

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

Voici l'ensemble des résultats.Here is the result set.

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

Forcer la recompilation de procédureForcing the Procedure to Recompile

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.Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.

L.L. Utilisation de l'option WITH RECOMPILEUsing the WITH RECOMPILE option

Le WITH RECOMPILE clause est utile lorsque les paramètres fournis à la procédure ne sont pas typiques et lorsqu’un nouveau plan d’exécution ne doit pas être mis en cache ou stocké en mémoire.The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.

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éSetting the Security Context

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.Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.

M.M. Utilisation de la clause EXECUTE ASUsing the EXECUTE AS clause

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.The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. 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.In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.

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.N. Création de jeux d'autorisations personnalisésCreating custom permission sets

L'exemple suivant utilise EXECUTE AS pour créer des autorisations personnalisées pour une opération de base de données.The following example uses EXECUTE AS to create custom permissions for a database operation. Il n'est pas possible d'accorder des autorisations à certaines opérations, telles que TRUNCATE TABLE.Some operations such as TRUNCATE TABLE, do not have grantable permissions. 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.By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the procedure.

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

Exemples : Azure SQL Data WarehouseAzure SQL Data Warehouse et Parallel Data WarehouseParallel Data WarehouseExamples: Azure SQL Data WarehouseAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

O.O. Créer une procédure stockée qui s’exécute une instruction SELECTCreate a Stored Procedure that runs a SELECT statement

Cet exemple montre la syntaxe de base pour la création et exécution d’une procédure.This example shows the basic syntax for creating and running a procedure. Lors de l’exécution d’un lot, CREATE PROCEDURE doit être la première instruction.When running a batch, CREATE PROCEDURE must be the first statement. Par exemple, pour créer la procédure stockée dans AdventureWorksPDW2012AdventureWorksPDW2012, d’abord définir le contexte de base de données, puis exécutez l’instruction CREATE PROCEDURE.For example, to create the following stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, set the database context first, and then run the CREATE PROCEDURE statement.

-- Uses AdventureWorksDW database  

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

Voir aussiSee Also

ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
Langage de contrôle de flux ( Transact-SQL ) Control-of-Flow Language (Transact-SQL)
Curseurs Cursors
Types de données (Transact-SQL) Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
DROP PROCEDURE ( Transact-SQL ) DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
EXECUTE AS ( Transact-SQL ) EXECUTE AS (Transact-SQL)
Procédures stockées (moteur de base de données) Stored Procedures (Database Engine)
sp_procoption ( Transact-SQL ) sp_procoption (Transact-SQL)
sp_recompile ( Transact-SQL ) sp_recompile (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.procedures (Transact-SQL) sys.procedures (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
fonctionnalité sys.numbered_procedures ( Transact-SQL ) sys.numbered_procedures (Transact-SQL)
Sys.numbered_procedure_parameters ( Transact-SQL ) sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
Créer une procédure stockée Create a Stored Procedure
Utilisez la Table-Valued paramètres ( moteur de base de données ) Use Table-Valued Parameters (Database Engine)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)