[SQLEXP2005][TSQL]Procédures avec Paramètres optionnels

**hsii04** · 01/03/2008, 14h38

Bonjour,

Je suis en train de travailler sur un site d'offres d'emploi, j'ai quelques questions au sujet des procédures stockées.
J'ai plusieurs paramètres qui peuvent ne pas être renseignés et j'aimerais savoir comment faire ma requète (voici la procédure)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
ALTER PROCEDURE [dbo].[GetOffres]
(
@RecruteurID int = null, 
@MetierID int = null,
@SecteurID int = null,
@NiveauID int = null,
@Reference nvarchar = null,
@Keyword nvarchar = null
)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Offres
WHERE
RecruteurID = @RecruteurID
AND
MetierID IN (@MetierID)
AND
SecteurID IN (@SecteurID)
AND
NiveauID = @NiveauID
AND
Reference = @Reference
AND
Titre LIKE @Keyword
END

Si je ne renseigne pas certain paramètres, ma requête ne me retournera aucun enregistrement
Serait-ce une bonne idée de faire ceci par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ALTER PROCEDURE [dbo].[GetOffres]
(
@RecruteurID int = null, 
@MetierID int = null,
@SecteurID int = null,
@NiveauID int = null,
@Reference nvarchar = null,
@Keyword nvarchar = null
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL = 'SELECT ...'
IF @RecruteurID IS NOT NULL
@SQL = @SQL + 'RecruteurID = ' + @RecrteurID
END IF
...
EXEC(@SQL)
END

De plus j'aimerais savoir comment faire pour passer des paramètres tels que @MetierID afin que ma requête s'effectue de cette sorte MetierID IN (@MetierID)

Merci d'avance de votre aide

**SQLpro** · 02/03/2008, 23h05

le conteneur d'un IN étant une liste ce n'est pas une valeur. Vous ne pouvez pas paramétrer un IN de la sorte.

A +

**sefaxe82** · 03/03/2008, 14h04

Bonjour,
C'est possible de parametrer un IN. (Voir le code).
Hsii04 je te propose cette procédure stockée. Elle prend en compte si un ou +sieurs paramètres sont null.
N'hésite pas à poser des questions.
Bonne journée à tous.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
ALTER PROCEDURE [GetOffres]
(
@RecruteurID varchar(8000) = NULL, 
@MetierID varchar(8000) = NULL,    -- la valeur passée doit être sous la forme suivante : int,int,int,...,int  (des integers séparés par des virgules)
@SecteurID varchar(8000) = NULL,   -- idem. (des integer séparés par des virgules) 
@NiveauID varchar(8000) = NULL,
@Reference nvarchar = NULL,
@Keyword nvarchar = NULL
)
AS
BEGIN
SET NOCOUNT ON;
--
    IF @RecruteurID	= '' SET @RecruteurID	= NULL
    IF @MetierID	= '' SET @MetierID		= NULL
    IF @SecteurID	= '' SET @SecteurID		= NULL
    IF @Reference	= '' SET @Reference		= NULL
	IF @Keyword		= '' SET @Keyword		= NULL
--
DECLARE @SQL AS VARCHAR(8000)
SET @SQL = 'SELECT * FROM Offres WHERE 
RecruteurID LIKE ('''+ISNULL(@RecruteurID,'%')+''')
AND
MetierID IN ('+ISNULL(@MetierID,'SELECT DISTINCT MetierID FROM Offres')+') 
AND
SecteurID IN ('+ISNULL(@SecteurID,'SELECT DISTINCT SecteurID FROM Offres')+') 
AND
NiveauID LIKE ('''+ISNULL(@NiveauID,'%')+''')
AND
Reference LIKE ('''+ ISNULL(@Reference,'%')+''') 
AND
Titre LIKE ('''+ ISNULL(@Keyword,'%')+''')'
 
EXEC (@SQL)
 
END

**hsii04** · 06/03/2008, 20h44

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
ALTER PROCEDURE [dbo].[GetOffres]
(
	@OffreID int = NULL,
	@RecruteurID int = NULL, 
	@MetierID nvarchar(MAX) = NULL,
	@SecteurID nvarchar(MAX) = NULL,
	@NiveauID nvarchar(MAX) = NULL,
	@RegionID nvarchar(MAX) = NULL,
	@ContratID nvarchar(MAX) = NULL,
	@Reference nvarchar = NULL,
	@Keyword nvarchar = NULL
)
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT *
		FROM Offres
	WHERE
		(OffreID = @OffreID OR @OffreID IS NULL)
		AND
		(RecruteurID = @RecruteurID OR @RecruteurID IS NULL)
		AND
		(MetierID IN (@MetierID) OR @MetierID IS NULL)
		AND
		(SecteurID IN (@SecteurID) OR @SecteurID IS NULL)
		AND
		(NiveauID IN (@NiveauID) OR @NiveauID IS NULL)
		AND
		(OffreID IN (SELECT OffreID FROM Offres_Regions WHERE RegionID IN (@RegionID)) OR @RegionID IS NULL)
		AND
		(OffreID IN (SELECT OffreID FROM Offres_Contrats WHERE ContratID IN (@ContratID)) OR @ContratID IS NULL)
		AND
		(Reference = @Reference OR @Reference IS NULL)
		AND
		(Titre LIKE @Keyword OR Description LIKE @Keyword OR @Keyword IS NULL)
END

Voici ma procédure modifié mais malheureusement j'obtiens ce message d'erreur lorsque je renseigne @MetierID = N'1,2,3' :
Msg*245, Niveau*16, État*1, Procédure*GetOffres, Ligne*22
Conversion failed when converting the nvarchar value '1,2,3' to data type int.

Un problème de conversion

Merci de vos réponses.

**elsuket** · 06/03/2008, 23h29

Bonjour,

Pour faire ce que tu veux, comme tu utilises une clause IN dans ta requête, il te faut créer une petite fonction qui te permettra de récupérer les valeurs que tu auras stockées dans une chaîne, séparées par des virgules (par exemple):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE FUNCTION Fn_ParseStringToInt (@str VARCHAR(8000))
	RETURNS @strVal TABLE (val INT)
AS
BEGIN
	DECLARE @pos SMALLINT,
			@subStr VARCHAR(8000);
 
	IF RIGHT(RTRIM(@str),1) != ','
		SET @str = @str  + ',';
 
	SET @pos =  PATINDEX('%,%' , @str);
	WHILE @pos != 0
	BEGIN
		SET @subStr = LEFT(@str, @pos - 1);
		INSERT @strVal VALUES (@subStr);
 
		SET @str = STUFF(@str, 1, @pos, '');
		SET @pos =  PATINDEX('%,%' , @str);
	END;
	RETURN;
END;
 
=> Si je fais :
 
SELECT val FROM Fn_ParseStringToInt ('1, 12, 123, 1234, 12345');
 
=> J'obtiens:
 
val
-----------
1
12
123
1234
12345

Du coup, ta procédure stockée devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
ALTER PROCEDURE [dbo].[GetOffres]
	@OffreID INT = NULL,
	@RecruteurID int = NULL, 
	@MetierID VARCHAR(MAX) = NULL,
	@SecteurID VARCHAR(MAX) = NULL,
	@NiveauID VARCHAR(MAX) = NULL,
	@RegionID VARCHAR(MAX) = NULL,
	@ContratID VARCHAR(MAX) = NULL,
	@Reference VARCHAR = NULL,
	@Keyword VARCHAR = NULL
AS
BEGIN
	SET NOCOUNT ON;
 
	IF
	(
		@OffreID IS NULL
		AND @RecruteurID IS NULL
		AND @MetierID IS NULL
		AND	@SecteurID	IS NULL
		AND @NiveauID IS NULL
		AND @RegionID IS NULL
		AND @ContratID IS NULL
		AND @Reference IS NULL
		AND @Keyword IS NULL
	)
	BEGIN
		RAISERROR ('[GetOffres] : aucun des parametres n''est renseigne !', 16, 1);
	END;
	ELSE
	BEGIN
		SELECT *
		FROM dbo.Offres OFFRES
		JOIN dbo.Offres_Regions REGIONS ON REGIONS.OffreID = OFFRES.OffreID
		JOIN dbo.Offres_Contrats CONTRATS ON CONTRATS.ContratID = OFFRES.ContratID
		WHERE (OffreID = @OffreID OR @OffreID IS NULL)
		AND (RecruteurID = @RecruteurID OR @RecruteurID IS NULL)
		AND (MetierID IN (SELECT val FROM dbo.Fn_ParseStringToInt (@MetierID)) OR @MetierID IS NULL)
		AND (SecteurID IN (SELECT val FROM dbo.Fn_ParseStringToInt (@SecteurID)) OR @SecteurID IS NULL)
		AND (NiveauID IN (SELECT val FROM dbo.Fn_ParseStringToInt (@NiveauID)) OR @NiveauID IS NULL)
		AND (OffreID IN (SELECT val FROM dbo.Fn_ParseStringToInt (@RegionID)) OR @RegionID IS NULL)
		AND (OffreID IN (SELECT val FROM dbo.Fn_ParseStringToInt (@ContratID)) OR @ContratID IS NULL)
		AND (Reference = @Reference OR @Reference IS NULL)
		AND (Titre LIKE @Keyword OR Description LIKE @Keyword OR @Keyword IS NULL)
	END;
END;

Là où je suis embêté c'est pour les clauses LIKE. D'une part parce qu'elles sont inutilisables en tant que telles dans ta procédure (Titre LIKE @Keyword <=> Titre = @Keyword alors que ce que tu veux faire c'est TITRE LIKE '%KEYWORD%'), et d'autre part parce que ce n'est pas très performant.

Je te conseille donc de créer un index full texte sur les colonnes Titre et Description et d'utiliser la commande FREETEXT (http://msdn2.microsoft.com/fr-fr/library/ms176078.aspx, exemple B

)

A+

**hsii04** · 08/03/2008, 15h12

Voici ma procédure finale pour ceux que ça peut servir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
ALTER PROCEDURE [dbo].[GetOffres]
(
	@OffreID int = NULL,
	@RecruteurID int = NULL, 
	@MetierID nvarchar(MAX) = NULL,
	@SecteurID nvarchar(MAX) = NULL,
	@NiveauID nvarchar(MAX) = NULL,
	@RegionID nvarchar(MAX) = NULL,
	@ContratID nvarchar(MAX) = NULL,
	@Reference nvarchar = NULL,
	@Keyword nvarchar = NULL
)
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT *
		FROM Offres
	WHERE
		(OffreID = @OffreID OR @OffreID IS NULL)
		AND
		(RecruteurID = @RecruteurID OR @RecruteurID IS NULL)
		AND
		(MetierID IN (SELECT val FROM dbo.ParseStringToInt(@MetierID)) OR @MetierID IS NULL)
		AND
		(SecteurID IN (SELECT val FROM dbo.ParseStringToInt(@SecteurID)) OR @SecteurID IS NULL)
		AND
		(NiveauID IN (SELECT val FROM dbo.ParseStringToInt(@NiveauID)) OR @NiveauID IS NULL)
		AND
		(OffreID IN (SELECT OffreID FROM Offres_Regions WHERE RegionID IN (SELECT val FROM dbo.ParseStringToInt(@RegionID))) OR @RegionID IS NULL)
		AND
		(OffreID IN (SELECT OffreID FROM Offres_Contrats WHERE ContratID IN (SELECT val FROM dbo.ParseStringToInt(@ContratID))) OR @ContratID IS NULL)
		AND
		(Reference = @Reference OR @Reference IS NULL)
		AND
		(OffreID IN (SELECT OffreID FROM Offres_TagsOffres WHERE TagOffreID IN (SELECT TagOffreID FROM TagsOffres WHERE Tag = @Keyword)) OR @Keyword IS NULL)
END

Pour ce qui est du LIKE '%%', j'ai opté vers un système de tags.