CREATE PROCEDURE (Transact-SQL)

Syntaxe

--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

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

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

Arguments

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

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

  Évitez l'utilisation du préfixe sp_ dans le nom des procédures. En effet, ce préfixe est utilisé par SQL Server pour faire référence aux procédures système. L'utilisation de ce préfixe peut entraîner l'échec du code de l'application s'il existe une procédure système portant le même nom. Pour plus d'informations, consultez Création de procédures stockées (moteur de base de données).

  Les procédures temporaires locales ou globales peuvent être créées en faisant précéder le nom de la procédure procedure_name, par un signe dièse unique (#procedure_name) pour les procédures temporaires locales, et par deux signes dièse (##procedure_name) pour les procédures temporaires globales. Une procédure temporaire locale n'est visible que par la connexion qui l'a créée et est automatiquement supprimée au moment de la déconnexion. Une procédure temporaire globale est disponible pour toutes les connexions et est supprimée à la fin de la dernière session qui l'utilise. Des noms temporaires ne peuvent pas être indiqués pour les procédures CLR.

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

• **;**number
  Entier facultatif qui regroupe les procédures de même nom. Ces procédures groupées peuvent être supprimées en même temps au moyen d'une seule instruction DROP PROCEDURE.

  Notes

  Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

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

• **@**parameter
  Paramètre déclaré dans la procédure. Spécifiez un nom de paramètre en plaçant le signe @ comme premier caractère. Ce nom doit respecter les règles applicables aux identificateurs. Un paramètre étant local à une procédure, vous pouvez utiliser le même nom dans d'autres procédures.

  Un ou plusieurs paramètres peuvent être déclarés, dans la limite de 2 100. La valeur de chaque paramètre déclaré doit être fournie par l'utilisateur lors de l'appel à la procédure, sauf si vous définissez une valeur par défaut pour le paramètre ou que sa valeur est définie sur un autre paramètre. Si une procédure contient des paramètres table et que le paramètre n'est pas indiqué dans l'appel, c'est une table vide qui est passée par défaut. Les paramètres ne peuvent que prendre la place d'expressions constantes ; ils ne peuvent pas être utilisés à la place de noms de tables, de colonnes ou d'autres objets de base de données. Pour plus d'informations, consultez EXECUTE (Transact-SQL).

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

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

  Instructions relatives aux types de données pour les procédures Transact-SQL :

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

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

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

  Instructions relatives aux types de données pour les procédures CLR :

  • Tous les types de données SQL Server natifs qui ont un équivalent en code managé peuvent être utilisés en tant que paramètres. Pour plus d'informations sur la correspondance entre les types de données CLR et les types SQL Server fournis par le système, consultez Mappage des données de paramètres CLR. Pour plus d'informations sur les types SQL Server de données système et leur syntaxe, consultez Types de données (Transact-SQL).

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

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

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

• default
  Valeur par défaut pour un paramètre. Si une valeur par défaut est définie pour un paramètre, la procédure peut être exécutée sans spécifier de valeur pour ce paramètre. La valeur par défaut doit être une constante ou il peut s'agir de la valeur NULL. La valeur constante peut être exprimée sous la forme d'un caractère générique, rendant ainsi possible l'utilisation du mot clé LIKE lors de la transmission du paramètre à la procédure. Voir exemple C ci-dessous.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  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 CLR (Common Language Runtime) ; toutefois, vous ne pouvez pas exécuter ces références dans SQL Server tant que vous n'avez pas activé l'option CLR activé. Pour activer cette option, utilisez sp_configure.

Recommandations

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

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

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

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

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

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

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

• Utilisez des transactions explicites avec BEGIN/END TRANSACTION et gardez les transactions aussi courtes que possible. Les transactions plus longues entraînent un verrouillage plus long des enregistrements et un plus grand risque de blocage. Pour plus d'informations, consultez Verrouillage et gestion des versions de ligne, Compatibilité de verrouillage (moteur de base de données) ou Niveaux d'isolation du moteur de base de données.

• 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. Pour plus d'informations, consultez Utilisation de TRY...CATCH dans Transact-SQL.

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

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

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

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

Remarques d'ordre général

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

Les variables à l'intérieur de la procédure peuvent être des variables système ou des variables définies par l'utilisateur, telles que @@SPID.

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

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

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 au moyen du fournisseur managé de SQL Server, un niveau d'imbrication supplémentaire est ajouté à la transition du code managé à SQL.

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

Interopérabilité

Le Moteur de base de données enregistre les paramètres de SET QUOTED_IDENTIFIER et de SET ANSI_NULLS lors de la création ou de la modification d'une procédure Transact-SQL. Ces paramètres d'origine sont utilisés lors de l'exécution de la procédure. Par conséquent, tous les paramètres de la session cliente pour SET QUOTED_IDENTIFIER et SET ANSI_NULLS sont ignorés lors de l'exécution de la procédure.

D'autres options SET, telles que SET ARITHABORT, SET ANSI_WARNINGS ou SET ANSI_PADDINGS ne sont pas sauvegardées lorsqu'une procédure est créée ou modifiée. Si la logique de la procédure dépend d'un paramétrage particulier, insérez une instruction SET au début de la procédure pour assurer un paramétrage adéquat. Lorsqu'une instruction SET est exécutée à partir d'une procédure, les paramètres ne restent effectifs que jusqu'à la fin de l'exécution de la procédure. Les paramètres reprennent ensuite la valeur qu'ils avaient avant l'appel de la procédure. Ceci permet aux clients individuels de définir les options souhaitées sans affecter la logique de la procédure.

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

Limitations et restrictions

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

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

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. Pour plus d'informations, consultez Résolution de noms différée et compilation.

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. Toutefois, vous pouvez passer une fonction en tant que variable, comme le montre 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 pourront pas être restaurées. Les procédures distantes ne font pas partie des transactions. Pour plus d'informations, consultez Gestion des erreurs dans les procédures stockées distantes de serveur à serveur.

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

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

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

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

Métadonnées

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

Pour estimer la taille d'une procédure compilée, utilisez les Compteurs de l'Analyseur de performances Taux d'accès au cache, Pages du cache et Nombre d'objets cache. Pour plus d'informations, consultez SQL Server - Objet Plan Cache.

Sécurité

Autorisations

Exemples

Syntaxe de base

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

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

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

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

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

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

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

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

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

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

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)))+'.'

USE AdventureWorks2008R2;
GO

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

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[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 
    [AdventureWorks2008R2].[Person].[StateProvince];

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

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

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

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

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

EXEC HumanResources.Update_VacationHours 40;

Gestion des erreurs

USE AdventureWorks2008R2;
GO

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

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

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

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

GO
EXEC Production.uspDeleteWorkOrder 13;

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

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

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

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

DROP PROCEDURE Production.uspDeleteWorkOrder;

Obscurcissement de la définition de procédure

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

EXEC sp_helptext 'HumanResources.uspEncryptThis';

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

Recompilation forcée de la procédure

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

Définition du contexte de sécurité

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

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

Voir aussi

Référence

Concepts

Autres ressources