Discussion :

[dcarroz]

Bonjour,
je souhaite construire une procédure stockée dynamique qui va me retourner une liste d'utilisateurs lors d'une recherche. Je construis la procédure de manière dynamique car on ne sait pas à l'avance combien de champs de recherche seront renseignés à l'appel de la procédure. Pour infos, les champs possibles sont Nom, Prenom, Academie et Structure. La recherche s'effectue toujours sur l'ensemble des champs renseignés avec comme opérande ET.
Voici mon code

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
GO
/*
	DESCRIPTION	
		Récupère la liste des utilisateurs d'une recherche
 
	PARAMETRES en Entrée:
		- @LastName le nom de famille de l'utilisateur.
		- @FirstName le prénom de l'utilisateur.
		- @Structure la structure à laquelle appartient l'utilisateur.
		- @Academy l'académie de l'utilisateur.
 
 
*/
 
ALTER PROCEDURE SPR_List_Search_Users
	@LastName			nvarchar(50),
	@FirstName			nvarchar(50),
	@Structure			nvarchar(50),
	@Academy			nvarchar(50)
AS
 
BEGIN
DECLARE
	@Select nvarchar(100),
	@From nvarchar(20),
	@Where nvarchar(max),
	@Param nvarchar(max),
	@ParamNom nvarchar(50),
	@ParamPrenom nvarchar(50),
	@ParamStructure nvarchar(50),
	@ParamAcademy nvarchar(50),	
	@cmd nvarchar(max)
 
	SET @ParamNom = ''
	SET @ParamPrenom = ''
	SET @ParamStructure = ''
	SET @ParamAcademy = ''
	SET @Select = 'SELECT 
			UserId ,
			Nom ,
			Prenom ,
			Email '
			-- TODO : rajouter la colonne structure
			--,Structure 'Structure'
	print 'SELECT = ' + @Select
	SET @From = ' FROM
		dbo.Users '
	print 'FROM = ' + @From	
		--Condition toujours vraie
		--nécessaire pour l'ajout des autres conditions 
	SET @Where = ' WHERE 1=1 '
	print 'WHERE 1 = ' +@Where
	IF  @LastName is not null
	BEGIN
		SET @Where = @Where + ' AND Nom = @LastName '
		print 'WHERE NOM = ' + @Where
		SET @ParamNom = 'SET @LastName = ' +  @LastName + ' '
		print 'NOM = ' + @ParamNom
	END
	IF @FirstName is not null
	BEGIN
		SET @Where = @Where + ' AND Prenom = @FirstName '
		print 'WHERE PRENOM = ' + @Where
		SET @ParamPrenom = 'SET @FirstName = ' +  @FirstName + ' '
		print 'PRENOM = ' + @ParamPrenom
	END
	--TODO : prendre en compte les 2 colonnes à rajouter dans la table
	--IF @Structure <> null
	--BEGIN
	--	SET @Where = @Where + ' AND Structure = @Structure '
	--  print 'WHERE STRUCTURE = ' + @Where
	--	SET @ParamStructure = 'SET @ParamStructure = ' + N'@Structure' + ' '
	--	print 'STRUCTURE = ' + @ParamStructure
	--END
	--IF @Academy <> null
	--BEGIN
	--	SET @Where = @Where + ' AND Academy = @Academy '
	--  print 'WHERE ACADEMY = ' + @Where
	--	SET @ParamAcademy = 'SET @ParamAcademy = ' + N'@Academy' + ' '
	--	print 'ACADEMY = ' + @ParamAcademy
	--END
 
	SET @Param = ' DECLARE @LastName nvarchar(50),
						  @FirstName nvarchar(50),
						  @Structure nvarchar(50),
						  @Academy nvarchar(50) '
	print 'PARAM = ' + @Param				  
	--' @LastName = ' + @LastName + ' ,@FirstName = ' + @FirstName + ' ,@Structure = ' + @Structure + ',@Academy = ' + @Academy
	SET @Param = @Param + @ParamNom + @ParamPrenom + @ParamStructure + @ParamAcademy 
	print 'PARAM FIN = ' + @Param
	SET @cmd = @Param +@Select + @From + @Where
	print 'CMD = ' + @cmd
	exec @cmd
END
GO

Mon problème concernant les clauses WHERE, par exemple

Code :

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

1
2
3
4
5
6
7
8
 
IF  @LastName is not null
	BEGIN
		SET @Where = @Where + ' AND Nom = @LastName '
		print 'WHERE NOM = ' + @Where
		SET @ParamNom = 'SET @LastName = ' +  @LastName + ' '
		print 'NOM = ' + @ParamNom
	END

@LastName est bien remplacé par la valeur du champ correspondant, comme Dupont par exemple. Le problème, c'est que je voudrais plutot avoir 'Dupont' comme valeur. Comment rajouter les quotes?

Merci d'avance de votre aide.

[mikedavem]

Bonsoir,

Pour faire apparaître l'apostrophe en tant que caractère, il faut le doubler :

Essayez ceci :

Code :

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

SET @WHERE = @WHERE + ' AND Nom =  ''' + @LastName + '''

++

[dcarroz]

cela fonctionne ainsi, je te remercie.

Cependant, je rencontre un autre problème.
Lorsque j'exécute ma procédure, j'ai l'erreur suivant

Msg 203, Level 16, State 2, Procedure SPR_List_Search_Users, Line 92
Le nom ' DECLARE @LastName nvarchar(50),
@FirstName nvarchar(50),
@Structure nvarchar(50),
@Academy nvarchar(50) SET @LastName = 'admin' SELECT
UserId ,
Nom ,
Prenom ,
Email FROM
dbo.Users WHERE 1=1 AND Nom = @LastName ' n'est pas un identificateur valide.

Je ne vois pas d'où cela vient. En effet, si j'exécute la procédure créée dynamiquement,

Code :

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

1
2
3
4
5
6
7
8
9
10
 
DECLARE @LastName nvarchar(50),
						  @FirstName nvarchar(50),
						  @Structure nvarchar(50),
						  @Academy nvarchar(50) SET @LastName = 'admin' SELECT 
			UserId ,
			Nom ,
			Prenom ,
			Email  FROM
		dbo.Users  WHERE 1=1  AND Nom = @LastName

j'obtiens le bon résultat...

Je ne vois donc pas trop où est mon erreur puisque ma procédure crée un requete valable et exécutable...

[mikedavem]

En regardant votre procédure dans votre post du début je vois ceci :

Code :

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

SET @WHERE = @WHERE + ' AND Nom = @LastName '

Je pense que vous avez oublié d'extraire la variable @LastName. Dans votre cas la variable est considérée comme une chaîne de caractère ...

En essayant ceci ?

Code :

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

SET @WHERE = @WHERE + ' AND Nom = ''' + @LastName + '''

Pourquoi un WHERE 1=1 dans votre code ?

++

[Sergejack]

Je n'utiliserais pas un query dynamique pour ça, à ta place.

Code :

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

1
2
3
4
5
6
 
SELECT
...
WHERE (@firstname IS NULL OR <xxx> = @firstname)
AND (@lastname IS NULL OR <xxx> = @lastname)
...

À toi te voir.

[Sergejack]

Pourquoi un WHERE 1=1 dans votre code ?

++

C'est une manière de garantir que l'élément syntaxique "WHERE" apparaîtra dans le query.

[mikedavem]

C'est une manière de garantir que l'élément syntaxique "WHERE" apparaîtra dans le query.

Oui effectivement en relisant le code ca parait logique

Je préfére également la solution de Sergejack pour votre problème.

++

[dcarroz]

Merci pour vos réponse. J'ai finalement utilisé la solution proposée par Sergejack, qui est plus simple que de passer par une query dynamique.