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

Version imprimable

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

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:

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:

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

02/03/2008, 23h05
SQLpro

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 +

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:

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

J'y suis presque

Code:

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.

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:

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:

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+

C'est bon merci à tous

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

Code:

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.

12/03/2008, 10h50
Fleur-Anne.Blain

N'oubli pas de cliquer sur :resolu: quand ton sujet est clos ;)