Procédure stockée avec construction dynamique de requête

**droliprane** · 25/11/2014, 10h50

Bonjour,

j'essaie de mettre au point une procédure stockée qui va me calculer le chiffre d'affaire réalisable pour une journée donnée, et en fonction de quelques critères de filtrage qui peuvent être actifs dans mon ihm (je peux filtrer sur un client en particulier ou bien sur une partie de son code, de même que sur un article précis ou sur ne partie de la référence).

Voici le code SQL :

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
 
USE [GPAO]
GO
/****** Objet :  StoredProcedure [dbo].[caRealisable]    Date de génération du script : 11/25/2014 10:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[caRealisable]
	-- Add the parameters for the stored procedure here
	@jour datetime,
	@client int,
	@article int,
	@filtre_client varchar(50),
	@filtre_article varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	DECLARE @rq varchar(500); 
        DECLARE @qte decimal(19,6);
	DECLARE @chiffre decimal(19,6);
 
	SET @rq = 'SELECT @qte = COALESCE(sum(ccl_qte_cde - ccl_qte_livree),0), @chiffre = COALESCE(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix),0) 
		FROM cc, clt, ccl, art 
		WHERE cc_confirme = 1 AND ccl_delai_confirme = @jour AND cc_id = ccl_cc_id AND cc_client = clt_id AND ccl_article_id = art_id';
 
	IF @client > 0
		SET @rq = @rq + ' AND clt_id = ' + @client;
	ELSE
		SET @rq = @rq + ' AND clt_code LIKE ''%' + @filtre_client + '%''';
 
	IF @article > 0
		SET @rq = @rq + ' AND art_id = ' + @article;
	ELSE
		SET @rq = @rq + ' AND art_reference LIKE ''%' + @filtre_article + '%''';
 
	EXECUTE @rq;  
 
END

Et l'erreur à l'exécution :

Msg 203, Niveau 16, État 2, Procédure caRealisable, Ligne 37
Le nom 'SELECT @qte = COALESCE(sum(ccl_qte_cde - ccl_qte_livree),0), @chiffre = COALESCE(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix),0)
FROM cc, clt, ccl, art
WHERE cc_confirme = 1 AND ccl_delai_confirme = @jour AND cc_id = ccl_cc_id AND cc_client = clt_id AND ccl_article_id = art_id AND clt_code LIKE '%%' AND art_reference LIKE '%%'' n'est pas un identificateur valide.

En exécutant ce scénario de test tout bête généré depuis SSMS:

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
 
USE [GPAO]
GO
 
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[caRealisable]
		@jour = '20141125',
		@client = 0,
		@article = 0,
		@filtre_client = '',
		@filtre_article = ''
 
SELECT	'Return Value' = @return_value
 
GO

Quelqu'un peut-il m'aider svp ?

Merci

**aieeeuuuuu** · 25/11/2014, 11h21

Bonjour,

Il manque des parenthèses pour EXECUTE :

Code :

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

1
2
 
EXECUTE (@req)

Attention, en procédant ainsi, votre application est sensible aux attaques par injection SQL.

**droliprane** · 25/11/2014, 11h29

Voici maintenant à quoi ressemble 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
 
USE [GPAO]
GO
/****** Objet :  StoredProcedure [dbo].[caRealisable]    Date de génération du script : 11/25/2014 11:20:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[caRealisable]
	-- Add the parameters for the stored procedure here
	@jour datetime,
	@client int,
	@article int,
	@filtre_client varchar(50),
	@filtre_article varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	DECLARE @rq NVarchar(4000); 
	DECLARE @ParamDefinition AS NVarchar(2000) 
 
    DECLARE @qte decimal(19,6);
	DECLARE @chiffre decimal(19,6);
 
	SET @rq = 'SELECT @qte = COALESCE(sum(ccl_qte_cde - ccl_qte_livree),0), @chiffre = COALESCE(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix),0) 
		FROM cc, clt, ccl, art 
		WHERE cc_confirme = 1 AND ccl_delai_confirme = ' + @jour + ' AND cc_id = ccl_cc_id AND cc_client = clt_id AND ccl_article_id = art_id';
 
	IF @client > 0
		SET @rq = @rq + ' AND clt_id = ' + @client;
	ELSE
		SET @rq = @rq + ' AND clt_code LIKE ''%' + @filtre_client + '%''';
 
	IF @article > 0
		SET @rq = @rq + ' AND art_id = ' + @article;
	ELSE
		SET @rq = @rq + ' AND art_reference LIKE ''%' + @filtre_article + '%'''; 
 
	Set @ParamDefinition =      
	      ' @qte decimal(19,6),
		@chiffre decimal(19,6),
		@jour datetime,
                @client int,
                @article int,
                @filtre_client varchar(50),
                @filtre_article  varchar(50)'
 
 
	EXECUTE sp_ExecuteSql @rq,
		@ParamDefinition, 
		@qte,
		@chiffre,
                @jour, 
                @client, 
                @article, 
                @filtre_client, 
                @filtre_article
 
	SELECT @rq, @qte, @chiffre;
 
	If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
 
ErrorHandler:
    Return(@@ERROR)
 
END

Mais j'obtiens cette erreur :

Msg 241, Niveau 16, État 1, Procédure caRealisable, Ligne 25
Échec de la conversion d'une valeur datetime à partir d'une chaîne de caractères.

Pas moyen d'arriver à lui faire comprendre ce paramètre @jour ...

**al1_24** · 25/11/2014, 11h35

Lors de la mise au point d'une procédure contenant une requête créée dynamiquement, il est souvent intéressant de demander l'affichage du texte de cette requête...
Essaye un peu pour voir

**droliprane** · 25/11/2014, 11h43

Envoyé par al1_24

Lors de la mise au point d'une procédure contenant une requête créée dynamiquement, il est souvent intéressant de demander l'affichage du texte de cette requête...
Essaye un peu pour voir

En fait je ne peux faire un select @rq que si je noie le paramètre @jour dans la chaine de ma requête, comme ceci :

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
SET @rq = 'SELECT @qte = COALESCE(sum(ccl_qte_cde - ccl_qte_livree),0), @chiffre = COALESCE(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix),0) 
		FROM cc, clt, ccl, art 
		WHERE cc_confirme = 1 AND ccl_delai_confirme = @jour AND cc_id = ccl_cc_id AND cc_client = clt_id AND ccl_article_id = art_id';
 
	IF @client > 0
		SET @rq = @rq + ' AND clt_id = ' + @client;
	ELSE
		SET @rq = @rq + ' AND clt_code LIKE ''%' + @filtre_client + '%''';
 
	IF @article > 0
		SET @rq = @rq + ' AND art_id = ' + @article;
	ELSE
		SET @rq = @rq + ' AND art_reference LIKE ''%' + @filtre_article + '%'''; 
 
	select @rq;

Et au final la chaine @rq contient ceci :

Code :

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

1
2
3
SELECT @qte = COALESCE(sum(ccl_qte_cde - ccl_qte_livree),0), @chiffre = COALESCE(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix),0)     
FROM cc, clt, ccl, art     
WHERE cc_confirme = 1 AND ccl_delai_confirme = @jour AND cc_id = ccl_cc_id AND cc_client = clt_id AND ccl_article_id = art_id AND clt_code LIKE '%%' AND art_reference LIKE '%%'

Il n'a pas interprêté mon paramètre @jour, forcément....

**iberserk** · 25/11/2014, 11h50

la dernière facon de faire est la bonne, en parameterisant la variable ,@jour, qu'est est votre erreur avec la dernière syntaxe?

**aieeeuuuuu** · 25/11/2014, 11h52

En effet.

Pourquoi faire du SQL Dynamique :
-vous multipliez les plan de requete (ça peut être un avantage dans certains cas...)
-vous êtes sensibles aux injections SQL
- [mode subjectif] c'est moins lisible et plus difficile à maintenir[/mode subjectif]

Pour votre procédure stockée, il me semble que vous pouvez simplement faire comme ceci (et les deux dernier filtres peuvent certainement être simplifiés en fonction de vos règles métier et de la logique globale.)

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
 
 
USE [GPAO]
GO
/****** Objet :  StoredProcedure [dbo].[caRealisable]    Date de génération du script : 11/25/2014 11:20:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[caRealisable]
	-- Add the parameters for the stored procedure here
	@jour datetime,
	@client int,
	@article int,
	@filtre_client varchar(50),
	@filtre_article varchar(50)
AS
BEGIN
	SET NOCOUNT ON;
 
 
	SELECT 
			COALESCE(sum(ccl_qte_cde - ccl_qte_livree),0) AS Qte
		,	COALESCE(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix),0)  AS Chiffre
		FROM cc
		INNER JOIN ccl
			ON cc_id = ccl_cc_id
		INNER JOIN clt
			ON	cc_client = clt_id
		INNER JOIN art 
			ON ccl_article_id = art_id
		WHERE	cc_confirme = 1 
			AND	ccl_delai_confirme = @jour 
			AND (
					(@Client > 0 AND clt_id = @client)
					OR 
					(COALESCE(@client,0)<= 0 AND clt_code LIKE '%' + @filtre_client + '%')
				)
			AND (
					(@article > 0 AND art_id = @article)
					OR
					(COALESCE(@article, 0) <=0 AND art_reference LIKE '%' + @filtre_article + '%')
				)
 
 
END

**droliprane** · 25/11/2014, 14h01

Envoyé par aieeeuuuuu

En effet.

Pourquoi faire du SQL Dynamique :
-vous multipliez les plan de requete (ça peut être un avantage dans certains cas...)
-vous êtes sensibles aux injections SQL
- [mode subjectif] c'est moins lisible et plus difficile à maintenir[/mode subjectif]

Pour votre procédure stockée, il me semble que vous pouvez simplement faire comme ceci (et les deux dernier filtres peuvent certainement être simplifiés en fonction de vos règles métier et de la logique globale.)

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
 
 
USE [GPAO]
GO
/****** Objet :  StoredProcedure [dbo].[caRealisable]    Date de génération du script : 11/25/2014 11:20:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[caRealisable]
	-- Add the parameters for the stored procedure here
	@jour datetime,
	@client int,
	@article int,
	@filtre_client varchar(50),
	@filtre_article varchar(50)
AS
BEGIN
	SET NOCOUNT ON;
 
 
	SELECT 
			COALESCE(sum(ccl_qte_cde - ccl_qte_livree),0) AS Qte
		,	COALESCE(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix),0)  AS Chiffre
		FROM cc
		INNER JOIN ccl
			ON cc_id = ccl_cc_id
		INNER JOIN clt
			ON	cc_client = clt_id
		INNER JOIN art 
			ON ccl_article_id = art_id
		WHERE	cc_confirme = 1 
			AND	ccl_delai_confirme = @jour 
			AND (
					(@Client > 0 AND clt_id = @client)
					OR 
					(COALESCE(@client,0)<= 0 AND clt_code LIKE '%' + @filtre_client + '%')
				)
			AND (
					(@article > 0 AND art_id = @article)
					OR
					(COALESCE(@article, 0) <=0 AND art_reference LIKE '%' + @filtre_article + '%')
				)
 
 
END

C'est vrai que comme ça, ça fonctionne bien. Mais au sein même de cette première requête, je dois encore tester la devise de la commande (cc_devise) et si différente de la devise société (un paramètre que je passerai à la procédure) alors je devrai encore aller chercher le taux de change entre les devises pour convertir le prix unitaire de la ligne de commande (ccl_prix).

Est-ce que c'est faisable de monter cette algorithmique à partir de cette dernière requête "simplifiée" ?

**aieeeuuuuu** · 25/11/2014, 14h15

Oui, c'est bien entendu possible

**droliprane** · 25/11/2014, 16h12

Envoyé par aieeeuuuuu

Oui, c'est bien entendu possible

Avec un curseur et des variables pour accumuler les totaux ?

**aieeeuuuuu** · 25/11/2014, 16h27

non, simplement avec une jointure sur la table contenant les taux de change, puis en intégrant ce taux à votre calcul.

**droliprane** · 25/11/2014, 16h34

Envoyé par aieeeuuuuu

non, simplement avec une jointure sur la table contenant les taux de change, puis en intégrant ce taux à votre calcul.

c'est à mon avis un petit peu plus compliqué (je suis pas expert en sql) mais j'ai besoin de faire une jointure avec la table taux seulement quand les devises sont différentes, sinon c'est du 1 pour 1

ou alors il faut que je définisse un record dans ma table taux qui définisse le taux de 1€ = 1€ pour que la jointure systématique fonctionne...

**aieeeuuuuu** · 25/11/2014, 16h43

oui, ou faire une jointure externe.
Dans votre calcul ensuite, vous faites un prix * COALESCE(tauxChange, 1).

**droliprane** · 25/11/2014, 16h48

En fait je pense que la source du problème est finalement un défaut de conception du modèle de données.

Je m'en sortirai plus facilement en mémorisant le taux de change d'une commande dans la table cc. Dans la plupart des cas ce sera 1 quand la commande sera exprimée dans la même devise que la devise société, et dans certains cas j'enregistrerai le taux de change utilisé au moment de l'enregistrement de la commande (proposition issue de ma table de taux, ou bien pourra être forcé). En même temps ça me protégera d'une possible rupture d'intégrité référentielle si quelqu'un vient à modifier les lignes de taux de change dans la table taux, ou bien les périodes couvertes, etc...

Procédure stockée avec construction dynamique de requête

Développement SQL Server

Discussions similaires

Partager

Partager