• Article

Spécification d'une valeur de paramètre par défaut

Vous pouvez créer une procédure stockée contenant des paramètres facultatifs en attribuant à ces derniers une valeur par défaut. Lorsque la procédure est exécutée, la valeur par défaut est prise en compte si aucune autre valeur n'a été spécifiée.

L'attribution de valeurs par défaut est nécessaire car une erreur système est retournée si un paramètre d'une procédure stockée n'a pas de valeur par défaut et si le programme appelant ne fournit pas de valeur pour ce paramètre lors de l'exécution de la procédure.

S'il n'est pas possible d'attribuer une valeur par défaut appropriée à un paramètre donné, vous pouvez spécifier la valeur NULL, et faire en sorte que la procédure stockée retourne un message personnalisé lorsqu'elle est exécutée sans qu'aucune valeur n'ait été spécifiée pour ce paramètre.

Notes

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, 6xxx), elle doit figurer entre guillemets simples.

Exemples

L'exemple suivant crée la procédure usp_GetSalesYTD 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 stockée est exécutée sans qu'une valeur n'ait été spécifiée pour le paramètre @SalesPerson.

USE AdventureWorks2008R2;
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 stockée. La première instruction exécute la procédure stockée sans spécifier de valeur d'entrée. Les instructions de gestion des erreurs dans la procédure stockée 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 stored procedure without specifying an input value.
EXEC Sales.usp_GetSalesYTD;
GO
-- Run the stored procedure with an input value.
EXEC Sales.usp_GetSalesYTD N'Blythe';
GO

L'exemple suivant illustre la procédure my_proc avec des valeurs par défaut pour chacun des trois paramètres (@first, @second et @third), et les valeurs affichées lorsque la procédure est exécutée avec des valeurs de paramètre différentes :

IF OBJECT_ID('dbo.my_proc', 'P') IS NOT NULL
    DROP PROCEDURE dbo.my_proc;
GO
CREATE PROCEDURE dbo.my_proc
    @first int = NULL,  -- NULL default value
    @second int = 2,    -- Default value of 2
    @third int = 3      -- Default value of 3
AS 
    SET NOCOUNT ON;
    SELECT @first, @second, @third;
GO

EXECUTE dbo.my_proc; -- No parameters supplied
GO

Voici l'ensemble des résultats.

NULL  2  3

EXECUTE dbo.my_proc 10, 20, 30;-- All parameters supplied
GO

Voici l'ensemble des résultats.

10  20  30

EXECUTE dbo.my_proc @second = 500;  -- Only second parameter supplied by name
GO

Voici l'ensemble des résultats.

NULL  500  3

EXECUTE dbo.my_proc 40, @third = 50 -- Only first and third parameters
                                    -- are supplied.

Voici l'ensemble des résultats.

40  2  50