CREATE PROCEDURE (Transact-SQL)

Syntaxe

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 { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

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

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

Arguments

• schema_name
  Nom du schéma auquel appartient la procédure.

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

  Nous vous recommandons vivement de ne pas utiliser le préfixe sp_ dans le nom de la procédure. En effet, ce préfixe est utilisé par SQL Server pour faire référence aux procédures stockées système. 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. Des noms temporaires ne peuvent pas être indiqués pour les procédures stockées CLR.

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

• **;**number
  Entier facultatif utilisé pour les procédures de groupes portant le même nom. Ces procédures groupées peuvent être supprimées en même temps par le biais d'une seule instruction DROP PROCEDURE. Par exemple, les procédures utilisées avec une application appelée orders peuvent être nommées orderproc;1, orderproc;2, etc. L'instruction DROP PROCEDURE orderproc supprime le groupe tout entier. Si le nom contient des identificateurs délimités, le numéro ne doit pas être compris dans l'identificateur, utilisez le délimiteur adéquat autour de procedure_name uniquement.

  Les procédures stockées numérotées font l'objet des restrictions suivantes :

  • Elles ne peuvent pas utiliser les types personnalisés de données xml ou CLR.

  • Elles ne peuvent pas créer de repère de plan sur une procédure stockée numérotée.

  [!REMARQUE]

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

• **@**parameter
  Paramètre de la procédure. Vous pouvez déclarer un ou plusieurs paramètres dans une instruction CREATE PROCEDURE. 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. Une procédure stockée peut comprendre au maximum 2 100 paramètres. 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.

  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. 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 base de données. Pour plus d'informations, consultez EXECUTE (Transact-SQL).

  Les paramètres ne peuvent pas être déclarés si FOR REPLICATION est précisé.

• [ type_schema_name**.** ] data_type
  Type de données du paramètre et du schéma auquel le paramètre appartient. Tous les types de données peuvent être utilisés comme paramètre dans une procédure stockée Transact-SQL. Vous pouvez utiliser un type de table défini par l'utilisateur pour déclarer un paramètre table comme paramètre pour une procédure stockée Transact-SQL. Les paramètres table peuvent être spécifiés uniquement comme paramètres d'entrée et ils doivent être accompagnés d'un mot clé READONLY. Le type de données cursor ne peut être utilisé que dans des paramètres OUTPUT. Lorsque vous spécifiez un type de données cursor, vous devez également spécifier les mots clés VARYING et OUTPUT. Plusieurs paramètres de sortie portant le type de données cursor peuvent être spécifiés.

  Dans le cas de procédures stockées CLR, les types char, varchar, text, ntext, image, cursor, les types de tables définis par l'utilisateur et table ne peuvent servir de paramètre. 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, consultez Types de données (Transact-SQL).

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

  Si type_schema_name n'est pas précisé, le moteur de base de données SQL Server pointe sur type_name dans l'ordre suivant :

  • types de données SQL Server fournis par le système ;

  • schéma par défaut de l'utilisateur actuel dans la base de données actuelle ;

  • schéma dbo dans la base de données actuelle.

  Dans le cas de procédures stockées numérotées, le type de données ne peut correspondre au type xml ou au type CLR défini par l'utilisateur.

• VARYING
  Indique l'ensemble de résultats pris en charge sous forme de paramètre de sortie. Ce paramètre est construit dynamiquement par la procédure stockée ; il se peut donc que son contenu varie. S'applique uniquement aux paramètres de type cursor.

• default
  Valeur par défaut pour le paramètre. Si vous avez défini une valeur par défaut indiquée par default, 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 elle peut avoir la valeur NULL. Si la procédure utilise le paramètre associé au mot clé LIKE, elle peut contenir les caractères génériques suivants : % _ [] et [^].

  [!REMARQUE]

  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.

• OUTPUT
  Indique que le paramètre est un paramètre de sortie. La valeur de cette option peut être retournée à l'instruction EXECUTE appelante. Utilisez les paramètres OUTPUT pour retourner les valeurs à la procédure appelante. Les paramètres de type text, ntext et image ne peuvent servir de paramètres OUTPUT à moins que la procédure corresponde à une procédure CLR. Un paramètre de sortie qui utilise le mot clé OUTPUT peut être un espace réservé pour curseur, sauf si la procédure correspond à une procédure CLR (Common Language Runtime). Un type de table défini par l'utilisateur ne peut pas être spécifié comme paramètre OUTPUT d'une procédure stockée.

• 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 défini par l'utilisateur, READONLY doit être spécifié.

• RECOMPILE
  Indique que le moteur de base de données n'utilise pas le cache pour le plan de cette procédure et que la procédure est recompilée à l'exécution. Cette option ne peut pas être utilisée si FOR REPLICATION est indiqué. RECOMPILE ne peut pas être précisé pour une utilisation avec des procédures stockées CLR.

  Pour ordonner au moteur de base de données d'annuler les plans relatifs à des requêtes individuelles au sein d'une procédure stockée, utilisez dans ce cas l'indicateur de requête RECOMPILE. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL). N'utilisez cet indicateur que si des valeurs atypiques ou temporaires sont utilisées dans un sous-ensemble de requêtes appartenant à la procédure stockée.

• ENCRYPTION
  Indique que SQL Server se charge de convertir le texte d'origine provenant 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 n'importe quel 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 stockées CLR.

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

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

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

• FOR REPLICATION
  Indique que les procédures stockées créées pour la réplication ne peuvent pas être exécutées sur l'Abonné. Une procédure stockée créée avec l'option FOR REPLICATION est utilisée comme filtre de procédure stockée et n'est exécutée que pendant la réplication. Les paramètres ne peuvent pas être déclarés si FOR REPLICATION est précisé. FOR REPLICATION ne peut pas être précisé pour une utilisation avec des procédures stockées CLR. L'option RECOMPILE est ignorée lorsqu'une procédure est créée par le biais de FOR REPLICATION.

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

• <sql_statement>
  Une ou plusieurs instructions Transact-SQL à passer à la procédure. Pour plus d'informations sur les restrictions qui s'y appliquent, consultez la section Notes .

• 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 stockée 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.

  [!REMARQUE]

  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 faisant référence à des modules du CLR mais vous ne pouvez pas exécuter ces références dans SQL Server tant que l'option clr enabled n'est pas activée. sp_configure permet d'activer cette option.

Notes

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

Une procédure stockée définie par l'utilisateur ne peut être créée que dans la base de données actuelle. Les procédures temporaires constituent cependant l'exception car elles sont toujours créées dans tempdb. Si un nom de schéma n'est pas précisé, le schéma par défaut de l'utilisateur chargé de créer la procédure est utilisé. Pour plus d'informations sur les schémas, consultez Séparation du schéma et de l'utilisateur.

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

Par défaut, les paramètres peuvent accepter des valeurs NULL. Si une valeur de paramètre NULL est passée et que ce paramètre est utilisé dans une instruction CREATE TABLE ou ALTER TABLE dans laquelle la colonne référencée n'accepte pas les valeurs NULL, le moteur de base de données génère une erreur. Pour éviter ce cas de figure, ajoutez une logique programmée à la procédure ou utilisez une valeur par défaut pour la colonne (en utilisant le mot clé DEFAULT avec l'instruction CREATE TABLE ou ALTER TABLE).

Nous vous recommandons de préciser explicitement 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 stockée 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 stockée.

Utilisation des options SET

Utilisation de paramètres dans les procédures stockées CLR

Informations sur les procédures stockées

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

Résolution de noms différée

Exécution des procédures stockées

Paramètres utilisant le type de données cursor

Procédures stockées temporaires

Exécution automatique des procédures stockées

Imbrication des procédures stockées

Limitations de <sql_statement>

Autorisations

Nécessite l'autorisation CREATE PROCEDURE dans la base de données et l'autorisation ALTER sur le schéma dans lequel la procédure est créée.

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

Exemples

A. Utilisation d'une procédure simple

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

B. Utilisation d'une procédure simple avec des paramètres

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

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

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

D. Renvoi de plusieurs jeux de résultats

USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

E. Utilisation des paramètres OUTPUT

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

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.

F. Utilisation de l'option WITH RECOMPILE

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

G. Utilisation de l'option WITH ENCRYPTION

USE AdventureWorks;
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 EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

EXEC sp_helptext 'HumanResources.uspEncryptThis';

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

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

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

(1 row(s) affected)

H. Utilisation de la résolution différée des noms

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

I. Utilisation de la clause EXECUTE AS

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

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

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

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

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

Voir aussi

Tâches

Référence

Concepts

Autres ressources