Comment créer une procédure stockée SQL Server avec des paramètres ?

Comment créer puis exécuter une procédure stockée SQL Server avec un ou plusieurs paramètres et gérer les valeurs par défaut ?

Pour rappel, créer une procédure stockée SQL Server avec des paramètres revient à grouper et organiser un ensemble de commandes T-SQL avec des variables en entrées. En effet, comment utiliser une requête SELECT sur la table Clients dans une procédure stockée SQL Server ? Par exemple, au lieu d’afficher toutes les données de la table, pour afficher un seul client, il suffit de filtrer la table grâce au paramètre passé en entrée.

1. Préparer la table des clients utilisée dans la procédure

Avant d’utiliser le script de création de la procédure stockée, créer d’abord la table des Clients. Ensuite, utiliser ce script SQL Server pour créer la table des Clients. Pour rappel, voici le script pour créer la table depuis une fenêtre SSMS:

-- Créer la table CLIENTS avec la colonne NOM déclarée comme UNIQUE
CREATE TABLE [dbo].[CLIENTS] (
   [NOCLIENT] int IDENTITY(1,1),
   [NOM]       nvarchar(20) UNIQUE,
   [VILLE]     nvarchar(20)
)
GO

-- Insérer des données pour les exemples de manipulation
INSERT INTO dbo.CLIENTS ( NOM, VILLE ) VALUES ( N'MAMMADOU', 'Lyon');
INSERT INTO dbo.CLIENTS ( NOM, VILLE ) VALUES ( N'SERGEI', 'Lyon');
INSERT INTO dbo.CLIENTS ( NOM, VILLE ) VALUES ( N'CHRISTOPHE', 'Paris');

-- Vérifier des lignes insérées
SELECT * 
FROM dbo.CLIENTS;

2. Exemple de code pour une procédure avec un seul paramètre

Voici un exemple de procédure stockée SQL qui utilise un seul et unique paramètre. Ce paramètre est le numéro de client. La procédure affiche le client dont le numéro est passé en paramètre. Pour appeler la procédure stockée, exécuter le code comme suit avec EXEC ou EXECUTE. Définir la variable avec un numéro de client.

CREATE PROCEDURE uspGetClient 
	@NoClient INT
AS
	SELECT	*
	FROM	[dbo].[CLIENTS]
	WHERE	[NOCLIENT] = @NoClient
GO

EXEC uspGetClient @NoClient = 3;

Exécuter une procédure sans paramètre obligatoire génère une erreur

Dans l’exemple précédent, l’appel de la procédure utilise la valeur du paramètre. De la même manière, exécuter la procédure SQL Server sans définir de valeur au paramètre du numéro de client:

EXEC uspGetClient; 

Alors le message d’erreur SQL suivant est affiché car le paramètre numéro de client est obligatoire et n’est pas de valeur par défaut assignée :

Msg 201, Level 16, State 4, Procedure uspGetClient, Line 0
Procedure or function ‘uspGetClient’ expects parameter ‘@NoClient’, which was not supplied.

3. Valeur par défaut des variables dans une procédure stockée

Pour aller plus loin et gérer les valeurs par défaut, c’est simple, dans la plupart des cas, il est conseillé de passer une valeur aux paramètres. Dans la pratique, ce n’est pas toujours possible voire utile. Pour prévoir le cas où un appel à la procédure se fait sans paramètre, utiliser la valeur NULL. En effet NULL permet de créer une valeur de paramètre dans la procédure et éviter les erreurs.

Après cela, utiliser cette deuxième version de la procédure stockée sans paramètre. Cette fois, aucune erreur et la requête ne renvoie aucune ligne car tous les clients dans la table ont un numéro de client.

-- Tester si la procédure existe et la supprimer
IF EXISTS (
	SELECT name 
	FROM sysobjects 
	WHERE name = 'uspGetClient' AND type = 'P'
)
  DROP PROCEDURE uspGetClient
GO

-- Créer la procédure avec une valeur par défaut
CREATE PROCEDURE uspGetClient 
  @NoClient int = NULL
AS
  SELECT	*
  FROM	[dbo].[CLIENTS]
  WHERE	[NOCLIENT] = @NoClient
GO

-- Exécuter la procédure stockée sans passer de paramètre
EXEC uspGetClient; 

4. Procédure stockée SQL avec plusieurs paramètres

C’est la même chose qu’avec un paramètre unique, et il faut lister, créer et appeler chaque paramètre de manière isolée. Ainsi, lister et séparer par une virgule chaque paramètre et son type de données, comme dans cet exemple de code SQL :

-- Tester si la procédure existe et la supprimer
IF EXISTS (
	SELECT name 
	FROM sysobjects 
	WHERE name = 'uspGetClient' AND type = 'P'
)
  DROP PROCEDURE uspGetClient
GO

-- Créer la même procédure avec deux paramètres et les valeurs par défaut
CREATE PROCEDURE uspGetClient 
  @NoClient	int = NULL, 
  @Ville	nvarchar(20) = NULL
AS
  SELECT	*
  FROM	[dbo].[CLIENTS]
  WHERE	[NOCLIENT]	= @NoClient
    OR	[VILLE]		= @Ville
GO

-- Appeler la procédure avec différentes combinaisons de paramètres
EXEC uspGetClient @NoClient = 1, @Ville = 'Lyon';

EXEC uspGetClient @NoClient = 1;

EXEC uspGetClient @Ville = 'Paris';

En effet, on peut appeler chacun des paramètres seuls, ou les deux en même temps. Il est aussi possible de ne passer aucun paramètre car la procédure utilise alors les valeurs par défaut avec la valeur NULL.

Enfin, appeler la procédure stockée uspGetClient sans paramètre est aussi possible. Elle renvoie dans ce cas toutes les données depuis la table des clients. Car c’est l’opérateur logique OR qui est utilisé dans la clause WHERE et donc le filtre est sans effet.

5. Conclusion

En conclusion, cet article explique comment créer et exécuter des procédures stockées SQL Server avec des paramètres, et nous permet donc :

• De préparer la table des clients pour l’utilisation dans les procédures stockées.
• De créer des procédures avec un ou plusieurs paramètres.
• De gérer les valeurs par défaut pour les paramètres.
• D’exécuter les procédures avec différentes combinaisons de paramètres.

Ces connaissances permettent d’optimiser l’utilisation des procédures stockées SQL Server dans les projets, en tirant parti de la flexibilité et de la modularité qu’elles offrent. Voici un autre tutoriel qui explique comment créer une requête SQL pour insérer des données depuis une requête de sélection.

Requête SQL Server Insert Into depuis un Select

Vous trouvez cet article utile? Partagez-le.