Spécifiez des paramètres dans une procédure stockée
S’applique à : 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
En spécifiant les paramètres de la procédure, les programmes appelants peuvent passer des valeurs dans le corps de la procédure. Ces valeurs peuvent être utilisées à plusieurs fins pendant l'exécution de la procédure. Les paramètres de la procédure peuvent également retourner des valeurs au programme appelant si le paramètre est marqué comme paramètre OUTPUT.
Une procédure peut contenir jusqu'à 2100 paramètres ; chacun ayant un nom, un type de données et une direction. Éventuellement, les paramètres peuvent avoir des valeurs par défaut.
La section suivante fournit des informations sur la transmission des valeurs dans les paramètres et sur la façon dont chacun des attributs de paramètre est utilisé lors d'un appel de procédure.
Remarque
Reportez-vous à la série d’exemples de bases de données AdventureWorks pour les exercices de cet article. Pour plus d’informations, consultez Exemples de bases de données AdventureWorks.
Transférer des valeurs dans les paramètres
Les valeurs des paramètres fournies avec un appel de procédure doivent être des constantes ou une variable ; un nom de fonction ne peut pas être utilisé comme valeur de paramètre. Les variables peuvent être des variables système ou des variables définies par l’utilisateur, telles que @@spid.
Les exemples suivants illustrent la transmission de valeurs de paramètres à la procédure uspGetWhereUsedProductID. Ils indiquent comment transmettre des paramètres sous forme de constantes et de variables. Ils décrivent également comment utiliser une variable pour transmettre la valeur d'une fonction.
USE AdventureWorks2022;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
L’exemple suivant retourne une erreur, car une fonction ne peut pas être transférée en tant que valeur de paramètre.
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
Utilisez plutôt une variable pour transférer une valeur de fonction au paramètre, comme dans l’exemple suivant :
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
Spécifier les noms de paramètres
Dans le cadre de la création d'une procédure et de la déclaration d'un nom de paramètre, le nom de ce dernier doit commencer par un seul caractère @ et être unique dans la portée de la procédure.
En nommant explicitement les paramètres et en attribuant les valeurs appropriées à chaque paramètre dans un appel de procédure, il est possible de fournir les paramètres dans n'importe quel ordre. Par exemple, si la procédure my_proc attend trois paramètres nommés @first, @second, et @third, les valeurs qui lui sont transférées peuvent être assignées aux noms de paramètres de la façon suivante : EXECUTE my_proc @second = 2, @first = 1, @third = 3;.
Remarque
Si une valeur de paramètre est fournie sous la forme @parameter = value, tous les paramètres suivants doivent être fournis de cette manière. Si les valeurs des paramètres ne sont pas transmises sous la forme @parameter = value, elles doivent être fournies dans le même ordre (de gauche à droite) que les paramètres répertoriés dans l’instruction CREATE PROCEDURE. Il est recommandé de spécifier des noms de paramètres, à la fois pour une lisibilité et une compatibilité supérieures avec les futures versions de la procédure stockée.
Avertissement
Un paramètre transmis sous la forme @parameter = value, mais mal orthographié, générera une erreur et empêchera l’exécution de la procédure.
Spécifier les types de données des paramètres
Les paramètres doivent être définis avec un type de données lorsqu'ils sont déclarés dans une instruction CREATE PROCEDURE. Le type de données d'un paramètre détermine le type et la plage de valeurs admis pour le paramètre lorsque la procédure est appelée. Par exemple, si vous définissez un paramètre avec le type de données tinyint , seules les valeurs numériques comprises entre 0 et 255 sont acceptées lorsqu'elles sont passées à ce paramètre. Une erreur est renvoyée lorsqu'une procédure est exécutée avec une valeur incompatible avec le type de données.
Spécifier des valeurs de paramètres par défaut
Un paramètre est considéré comme facultatif si une valeur par défaut est spécifiée lorsqu'il est déclaré. Il n'est pas nécessaire de fournir une valeur pour un paramètre optionnel dans un appel de procédure.
La valeur par défaut d'un paramètre est utilisée lorsque :
- Aucune valeur n'est spécifiée pour le paramètre dans l'appel de procédure.
- Le mot clé DEFAULT est spécifié comme valeur dans l'appel de procédure.
Remarque
Si la valeur par défaut est une chaîne de caractères contenant des espaces ou des signes de ponctuation ou si elle débute par un nombre (par exemple, 6abc), elle doit figurer entre guillemets dactylographiques.
Remarque
Les paramètres par défaut ne sont pas pris en charge dans Azure Synapse Analytics ou Analytics Platform System (PDW).
Si aucune valeur ne peut être spécifiée correctement comme valeur par défaut pour le paramètre, spécifiez NULL comme valeur par défaut. Il est judicieux que la procédure retourne un message personnalisé lorsqu'elle est exécutée sans valeur pour le paramètre.
L'exemple suivant crée la procédure uspGetSalesYTD avec un paramètre d'entrée, @SalesPerson. La valeur NULL est désignée comme valeur par défaut pour le paramètre et est utilisée dans les instructions de gestion des erreurs afin de retourner un message d’erreur personnalisé lorsque la procédure est exécutée sans qu’une valeur n’ait été spécifiée pour le paramètre @SalesPerson.
USE AdventureWorks2022;
GO
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetSalesYTD
@SalesPerson nvarchar(50) = NULL -- NULL default value
AS
SET NOCOUNT ON;
-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
PRINT 'ERROR: You must specify the last name of the sales person.'
RETURN
END
-- Get the sales for the specified sales person and
-- assign it to the output parameter.
SELECT SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN
GO
L'exemple suivant exécute la procédure. La première instruction exécute la procédure sans spécifier de valeur d'entrée. Les instructions de gestion des erreurs dans la procédure retournent le message d'erreur personnalisé. La deuxième instruction fournit une valeur d'entrée et retourne l'ensemble de résultats attendu.
-- Run the procedure without specifying an input value.
EXEC Sales.uspGetSalesYTD;
GO
-- Run the procedure with an input value.
EXEC Sales.uspGetSalesYTD N'Blythe';
GO
Bien que vous puissiez omettre des paramètres ayant des valeurs par défaut, la liste des paramètres pouvant accepter la valeur Null peut seulement être tronquée. Par exemple, si une procédure a cinq paramètres, sans spécifier les noms des paramètres avec un @parameter = value, le quatrième et le cinquième paramètre peuvent être omis. Toutefois, le quatrième ne peut pas être ignoré tant que le cinquième est inclus, sauf si les paramètres sont fournis sous la forme @parameter = value.
Spécifier plusieurs paramètres avec des valeurs par défaut
Vous pouvez omettre les paramètres si vous spécifiez les noms des paramètres. Considérez la procédure stockée suivante avec plusieurs paramètres facultatifs avec des valeurs par défaut NULL.
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspSearchList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspSearchList;
GO
CREATE PROCEDURE Production.uspSearchList
@ListPrice money
, @ProductCategoryID int = NULL -- NULL default value
, @ProductSubcategoryID int = NULL -- NULL default value
, @ProductBusinessEntityID int = NULL -- NULL default value
AS
SET NOCOUNT ON;
SELECT
p.Name, p.Class, p.ListPrice, p.ProductID, pc.Name, psc.Name, v.Name
FROM
Production.Product AS p
INNER JOIN Production.ProductSubCategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID
INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID
WHERE (p.ListPrice < @ListPrice)
AND (pc.ProductCategoryID = @ProductCategoryID or @ProductCategoryID IS NULL)
AND (psc.ProductSubcategoryID = @ProductSubcategoryID or @ProductSubcategoryID IS NULL)
AND (pv.BusinessEntityID = @ProductBusinessEntityID or @ProductBusinessEntityID IS NULL);
GO
Vous pouvez spécifier ou omettre des paramètres avec des valeurs par défaut, comme le montre la série d’exemples suivants, à condition que chacun soit fourni avec son nom de paramètre sous la forme @parameter = value :
--Find all Products with a list price less than 150.00 and in the ProductCategoryID = 4
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36
EXEC Production.uspSearchList @ListPrice = 150, @ProductSubCategoryID = 36;
--Find all Products with a list price less than 150.00 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductBusinessEntityID = 1498;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4, @ProductBusinessEntityID = 1498;
L’exemple suivant n’est pas une syntaxe T-SQL valide, car tous les paramètres suivants doivent être fournis de la même façon, une fois qu’un nom de paramètre est fourni. Il est toujours recommandé de fournir des noms de paramètres pour toutes les valeurs afin d’éviter les erreurs et les confusions.
EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;
Spécifier la direction du paramètre
La direction d'un paramètre peut être une entrée (une valeur passée dans le corps de la procédure) ou une sortie (la procédure retourne une valeur au programme appelant). La valeur par défaut est un paramètre d'entrée.
Pour spécifier un paramètre de sortie, vous devez indiquer le mot clé OUTPUT dans la définition du paramètre contenue dans l'instruction CREATE PROCEDURE. La procédure retourne la valeur actuelle du paramètre de sortie au programme appelant lorsqu'elle se termine. Le programme appelant doit également utiliser le mot clé OUTPUT lorsqu'il exécute la procédure pour enregistrer la valeur du paramètre dans une variable, qu'il pourra ensuite utiliser.
L'exemple suivant crée la procédure Production.usp_GetList qui retourne la liste des produits dont le prix ne dépasse pas un montant spécifié. Cet exemple représente l'utilisation de plusieurs instructions SELECT et de plusieurs paramètres OUTPUT. Les paramètres OUTPUT permettent à une procédure externe, à un lot ou à plusieurs instructions Transact-SQL d'accéder à un ensemble de valeurs pendant l'exécution de la procédure.
USE AdventureWorks2022;
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
Exécutez usp_GetList afin de retourner la liste des produits Adventure Works (Bikes) et coûtant moins de 700 $. Les paramètres OUTPUT @cost et @compareprices sont utilisés en conjonction avec un langage de contrôle de flux afin de retourner un message dans la fenêtre Messages .
Remarque
La variable OUTPUT doit être définie pendant la création de la procédure et pendant l'utilisation de la variable. Le nom du paramètre et celui de la variable ne doivent pas nécessairement correspondre. En revanche, le type de données et la position du paramètre doivent correspondre (sauf si vous utilisez @listprice = variable).
DECLARE @ComparePrice money, @Cost money ;
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.';
Voici le jeu de résultats partiel :
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.