Spécifier des paramètres dans une procédure stockée
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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.
Notes
Reportez-vous à la AdventureWorks série d’exemples de bases de données pour les exercices de cet article. Pour plus d’informations, consultez Exemples de bases de données AdventureWorks.
Passer des valeurs dans des 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 définies par l’utilisateur ou des variables système 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 passé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 passer 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 des 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, @secondet @third, les valeurs passées à la procédure peuvent être affectées aux noms de paramètres, tels que : EXECUTE my_proc @second = 2, @first = 1, @third = 3;.
Notes
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 de paramètre ne sont pas transmises sous la forme @parameter = value, les valeurs doivent être fournies dans l’ordre identique (de gauche à droite), car les paramètres sont 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 versions futures de la procédure stockée.
Avertissement
Tout paramètre passé dans le formulaire @parameter = value avec le paramètre mal orthographié entraîne SQL Server générer une erreur et empêcher l’exécution de la procédure.
Spécifier des types de données de paramètre
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 les valeurs par défaut des paramètres
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.
Notes
Si la valeur par défaut est une chaîne de caractères qui contient des vides incorporés ou une ponctuation, ou si elle commence par un nombre (par exemple, 6abc), elle doit être placée entre guillemets simples et droits.
Notes
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 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. NULL est attribué comme valeur par défaut pour le paramètre et est utilisé dans les instructions de gestion des erreurs pour renvoyer un message d’erreur personnalisé pour les cas où la procédure est exécutée sans valeur pour le @SalesPerson paramètre.
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 les paramètres pour lesquels des valeurs par défaut ont été fournies puissent être omis, la liste des paramètres non nullables ne peut être tronquée que. Par exemple, si une procédure a cinq paramètres, sans spécifier les noms de paramètres avec un @parameter = value, les quatrième et cinquième paramètres peuvent être omis. Toutefois, le quatrième paramètre ne peut pas être ignoré tant que le cinquième paramètre 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 NULL valeurs par défaut.
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 d’eux soit fourni avec son nom de paramètre au format @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 manière, 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 et d’éviter les erreurs et la confusion.
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 pour retourner une liste de produits Adventure Works (Bikes) dont le coût est inférieur à 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 .
Notes
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. Toutefois, le type de données et le positionnement du paramètre doivent correspondre (sauf si @listprice = variable est utilisé).
DECLARE @ComparePrice money, @Cost money ;
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.';
Voici le jeu de résultats partiel :
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.
Étapes suivantes
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour