Passer des paramètres à des procédures stockées

  • 3 minutes

L'un des avantages de l'utilisation des procédures stockées est que vous pouvez leur passer des paramètres lors de l'exécution. Les paramètres d’entrée peuvent être utilisés pour filtrer les résultats de la requête, comme dans le prédicat d’une clause WHERE, ou la valeur d’un opérateur TOP. 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. Vous pouvez également attribuer une valeur par défaut à un paramètre.

Paramètres d'entrée

Les procédures stockées déclarent leurs paramètres d'entrée par nom et type de données dans l'en-tête de l'instruction CREATE PROCEDURE. Le paramètre est ensuite utilisé comme une variable locale dans le corps de la procédure. Vous pouvez déclarer et utiliser plusieurs paramètres dans une procédure stockée. Les paramètres d'entrée constituent le type de paramètre par défaut.

Les noms des paramètres doivent être préfixés par le caractère @ et être uniques dans la portée de la procédure.

Pour passer un paramètre à une procédure stockée, utilisez la syntaxe suivante :

 EXEC <schema_name>.<procedure_name> @<parameter_name> = 'VALUE'

Par exemple, une procédure stockée appelée ProductsBySupplier dans le schéma Produits sera exécutée avec un paramètre supplierid en utilisant le code suivant :

EXEC Products.ProductsBySupplier @supplierid = 5

La meilleure pratique consiste à passer les valeurs des paramètres sous forme de paires nom-valeur. Les paramètres multiples sont séparés par des virgules. Par exemple, si le paramètre est appelé customerid et que la valeur à passer est 5, utilisez le code suivant :

EXEC customers.customerid @customerid=5

Vous pouvez également passer les paramètres par position, en omettant le nom du paramètre. Cependant, les paramètres doivent être passés soit par nom soit par position : vous ne pouvez pas combiner ces deux méthodes pour passer des paramètres à la procédure. Si les paramètres sont passés par ordre, ils doivent être dans le même ordre que celui dans lequel ils sont répertoriés dans l'instruction CREATE PROCEDURE.

Vous pouvez passer des valeurs comme une constante ou comme une variable, par exemple :

EXEC customers.customerid @CustomerID

En revanche, vous ne pouvez pas utiliser une fonction pour passer un paramètre. Par exemple, le code suivant génère une erreur :

EXEC customers.customerid GETDATE()

Vérifiez que les paramètres affichent le type de données correct. Par exemple, si une procédure accepte un paramètre NVARCHAR, passez le format de chaîne de caractères Unicode : N'string'.

Vous pouvez afficher les noms des paramètres et les types de données dans Azure Data Studio ou SQL Server Management Studio (SSMS). Développez la liste des objets de base de données jusqu’à ce que le dossier Procédures stockées s’affiche sous le dossier Programmabilité .

Diagramme montrant « Développer le dossier Programmation pour afficher les procédures stockées et les types de données des paramètres ».

Les noms en deux parties des procédures stockées apparaissent, ainsi qu'un dossier Paramètres qui contient, pour chaque paramètre :

  • Nom du paramètre.
  • Type de données.
  • Une flèche d’entrée indiquant un paramètre d’entrée.
  • Une flèche de sortie indiquant un paramètre de sortie.

Vous pouvez consulter une vue du catalogue système, par exemple sys.parameters, pour récupérer les définitions de paramètres avec l'ID de l'objet.

Valeurs par défaut

Si un paramètre a été déclaré avec une valeur par défaut, inutile de passer la valeur lorsque la procédure stockée est exécutée. Si une valeur est passée, elle sera utilisée ; mais si aucune valeur n'est passée, la valeur par défaut sera utilisée.

Lorsque la procédure stockée est créée, des valeurs par défaut sont attribuées aux paramètres à l’aide de l’opérateur =, par exemple :

CREATE PROCEDURE Sales.SalesYTD  
    -- Set NULL as the default value
    @SalesPerson nvarchar(50) = NULL 
    AS ...

Paramètres de sortie

Vous avez vu comment passer une valeur dans une procédure stockée, connue sous le nom de paramètre d’entrée.

Cependant, vous pouvez également renvoyer une valeur au programme appelant. C’est ce qu’on appelle un paramètre de sortie (OUTPUT). Utilisez le mot clé OUTPUT ou OUT pour spécifier un paramètre de sortie 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.

Dans le fragment de code T-SQL suivant, deux paramètres sont définis comme paramètres OUTPUT, @ComparePrice et @ListPrice.

CREATE PROCEDURE Production.uspGetList @Product varchar(40)
    , @MaxPrice money   
    , @ComparePrice money OUTPUT  
    , @ListPrice money OUT  
AS

Des valeurs sont ensuite attribuées aux paramètres OUTPUT dans le corps de la procédure stockée, par exemple SET @ComparePrice = @MaxPrice;.