procédures stockées dynamiques

**Chucky69** · 28/12/2005, 18h04

Bonjour,

Je travaille avec une base sql server 2000.

J'essaie de rendre dynamique une procédure stockée comme suit :

Code :

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

1
2
3
CREATE PROCEDURE [eds].[MaProc]
AS Select * from MaTable
GO

par :

Code :

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

1
2
3
4
5
6
CREATE PROCEDURE [eds].[MaProc]
(
@StringSql3 ntext
)
AS @StringSql3
GO

@StringSql3 est un paramètre généré et envoyé par ma page asp qui encapsule ma requête (Select * from MaTable par exemple).

En revanche, l'assistant de création de ma procédure stockée me trouve une erreur 170 : ligne 5 : syntaxe incorrecte vers '@StringSql3' (Après le AS) et ne veut pas créer ma procédure stockée.

J'espère avoir été assez claire sur mon problème.

Merci d'avance pour votre aide.

**inoussa** · 29/12/2005, 09h53

Code :

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

1
2
3
4
5
6
7
 
CREATE PROCEDURE [dbo].[MaProc]
(
@StringSql3 ntext
)
AS 
EXEC(@StringSql3)

Bien que ca soit une mauvaise idée!

**Chucky69** · 29/12/2005, 11h38

Merci inoussa pour ta réponse,

Pourquoi une mauvaise idée...problème de performance ?

**inoussa** · 29/12/2005, 12h21

Mauvais pour la sécurité. Cette page décrit bien un type d'attaque(SQL Injection) que tu subir avec ça.
Excellente journée.

**Chucky69** · 29/12/2005, 16h38

ok merci

J'essaie de procéder autrement (mais est-ce plus sécurisé ?) en mettant une partie de ma requête en dur dans ma procédure stockée dans laquelle je récupère diverses variables :

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
CREATE PROCEDURE [eds].[MappingMaster_ReferencesMois1]
(
	@PremiereDate DATETIME,
	@DerniereDate DATETIME,
	@NumNatureDispositif int
)
 AS
 SELECT TOP 100 PERCENT REFERENCE, DureeModule
 FROM TYPESDEFORMATION
 WHERE     (MappingON = - 1) 
 
AND (TYPESDEFORMATION.ActionCorrectriceON = 0)
 
 AND (DateDebutFormation BETWEEN CONVERT(DATETIME, @PremiereDate, 102)
 AND CONVERT(DATETIME, @DerniereDate, 102))
 AND (CodeNatureDispositif = @NumNatureDispositif)
 OR (MappingON = - 1) AND (TYPESDEFORMATION.ActionCorrectriceON = 0)
 AND (CodeNatureDispositif = @NumNatureDispositif)
 AND (DateFinFormation BETWEEN CONVERT(DATETIME, @PremiereDate, 102)
 AND CONVERT(DATETIME, @DerniereDate, 102))
 ORDER BY [N°DOMAINE], DateDebutFormation
GO

Lorsque j'execute ma procédure stockée de ma page asp ou de l'analyseur de requête elle fonctionne nickel...Mais...dès que je veux rendre dynamique la portion de requête suivante AND (TYPESDEFORMATION.ActionCorrectriceON = 0) je rencontre un problème avec l'assistant de création des procédures stockées qui me signale une erreur de syntaxe.

Ci-dessous, le contenu de ma nouvelle 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
CREATE PROCEDURE [eds].[MappingMaster_ReferencesMois1]
(
	@SuiteRequete NTEXT,
	@PremiereDate DATETIME,
	@DerniereDate DATETIME,
	@NumNatureDispositif int
)
 AS
 SELECT TOP 100 PERCENT REFERENCE, DureeModule
 FROM TYPESDEFORMATION
 WHERE     (MappingON = - 1) + @SuiteRequete +
 AND (DateDebutFormation BETWEEN CONVERT(DATETIME, @PremiereDate, 102)
 AND CONVERT(DATETIME, @DerniereDate, 102))
 AND (CodeNatureDispositif = @NumNatureDispositif)
 OR (MappingON = - 1) AND (TYPESDEFORMATION.ActionCorrectriceON = 0)
 AND (CodeNatureDispositif = @NumNatureDispositif)
 AND (DateFinFormation BETWEEN CONVERT(DATETIME, @PremiereDate, 102)
 AND CONVERT(DATETIME, @DerniereDate, 102))
 ORDER BY [N°DOMAINE], DateDebutFormation
GO

D'après mes recherches, la concaténation peut être effectuée avec les signes suivants :
+
ou
||
mais l'erreur de syntaxe est toujours signalée.

Au secours !!!

**inoussa** · 29/12/2005, 19h14

Avec la concaténation tu seras obligé de revenir à EXEC....

En quoi consiste la partie dynamique? Combien de paramètres contient-elle? Pourquoi ne pourrait-elle pas etre traitée par des paramètres de la procedure stokée?

**Chucky69** · 30/12/2005, 09h38

en fait, j'ai une page asp qui génère dynamiquement une partie de ma requête SQL en fonction des cas traités.

La gestion des variables numériques et dates fonctionnent très bien dans cette procédure sauf le rajout du paramètre @SuiteRequete qui sera vide ou complété dans cet exemple du code sql suivant AND (TYPESDEFORMATION.ActionCorrectriceON = 0).

La solution serait de créé autant de procédures qu'il y a de cas de requêtes, pour rajouter le petit bout de code SQL manquant selon les cas (2 possibilités pour cette requête mais j'ai d'autres requêtes qui ont plus de possibilités).

Pourquoi ne pourrait-elle pas etre traitée par des paramètres de la procedure stokée?

Etant très très novice en la matière, ce n'est pas ce que j'essaie de faire avec les différents paramètres envoyés à la procédure stockée ?

Merci

**inoussa** · 30/12/2005, 10h54

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
 
CREATE PROCEDURE [dbo].[MappingMaster_ReferencesMois1](
   @PremiereDate DATETIME,
   @DerniereDate DATETIME,
   @NumNatureDispositif int,
   @ActionCorrectriceON Int
) AS
 
  SELECT TOP 100 PERCENT REFERENCE, DureeModule
    FROM TYPESDEFORMATION
    WHERE ( MappingON = - 1 ) AND
          ( DateDebutFormation BETWEEN CONVERT(DATETIME, @PremiereDate, 102) AND CONVERT(DATETIME, @DerniereDate, 102) ) AND
          ( CodeNatureDispositif = @NumNatureDispositif )OR
          ( MappingON = - 1 ) AND
          ( CodeNatureDispositif = @NumNatureDispositif )AND
          ( DateFinFormation BETWEEN CONVERT(DATETIME, @PremiereDate, 102) AND CONVERT(DATETIME, @DerniereDate, 102) ) AND
 
          ( ( @ActionCorrectriceON IS NULL ) OR 
            ( TYPESDEFORMATION.ActionCorrectriceON = @ActionCorrectriceON )
          )
 
    ORDER BY [N°DOMAINE], DateDebutFormation
GO

Dans le cas où tu voudras filtrer sur "ActionCorrectriceON" alors dans l'appel passe une valeur au paramètre @ActionCorrectriceON,exemple

Code :

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

1
2
3
4
5
6
 
[dbo].[MappingMaster_ReferencesMois1]
  '01/01/2005',
  '31/01/2005',
  1,
  5 -- @ActionCorrectriceON

sinon tu lui passe NULL

Code :

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

1
2
3
4
5
6
 
[dbo].[MappingMaster_ReferencesMois1]
  '01/01/2005',
  '31/01/2005',
  1,
  NULL -- @ActionCorrectriceON

**Chucky69** · 30/12/2005, 12h41

mon champs ActionCorrectriceON n'a que 2 valeurs possible 0 ou -1.

en fonction du paramètre @NumNatureDispositif, je dois faire un filtre ou pas du tout sur ActionCorrectriceON, d'où mon besoin de rajouter ou non le critère AND (TYPESDEFORMATION.ActionCorrectriceON = 0) dans ma requête.

Sur cette même page, j'ai des requêtes où tous les critères vont être générés dynamiquement par mon script asp...je n'ai donc pas la possibilité de créer des conditions dans la procédure stockée.

Si j'essaie de transférer la partie SQL créée dynamiquement dans une variable en ntext, je n'arrive pas à l'insérer dans le select tel que tu me l'a appris.

Code :

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

select * from MaTable Where exec(@SuiteRequete) ORDER BY Id_

ou

Code :

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

select * from MaTable Where + exec(@SuiteRequete) + ORDER BY Id_

L'assistant me donne toujours une erreur de syntaxe.

**inoussa** · 30/12/2005, 15h44

Le code ci-apres montre comment prendre en compte des paramètres
( ou non ) dans ta procedure stockée. Certainement tu auras à le
peauffiner pour repondre à ton besoin.

Concernant la commande "EXEC[UTE]" :

Documentation en ligne SQL Server:
Exécute une fonction à valeurs scalaires définie par l'utilisateur,
une procédure système, une procédure stockée personnalisée
ou une procédure stockée étendue.
Prend également en charge l'exécution d'une chaîne de caractères à
l'intérieur d'un lot d'instructions Transact-SQL .

Tu ne peux pas l'appelant comme tu le fais.Regarde la documentation
pour du langage TRANSACT-SQL pour la syntaxe.

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
 
CREATE PROCEDURE [dbo].[MappingMaster_ReferencesMois1](
   @PremiereDate DATETIME,
   @DerniereDate DATETIME,
   @NumNatureDispositif int,
   @ActionCorrectriceON Int
) AS
 
  SELECT TOP 100 PERCENT REFERENCE, DureeModule
    FROM TYPESDEFORMATION
    WHERE ( ( @PremiereDate Is NULL ) OR -- Tu ne fais le filtre que si @PremiereDate n'est pas NULL!
            ( ( DateDebutFormation >= CONVERT(DATETIME, @PremiereDate, 102) ) AND
              ( DateFinFormation >= CONVERT(DATETIME, @PremiereDate, 102) )
            )
          ) AND
 
          ( ( @DerniereDate Is NULL ) OR -- Tu ne fais le filtre que si @DerniereDate n'est pas NULL!
            ( ( DateDebutFormation <= CONVERT(DATETIME, @DerniereDate, 102) ) AND
              ( DateFinFormation <= CONVERT(DATETIME, @DerniereDate, 102) )
            )
          ) AND
 
          ( ( @NumNatureDispositif Is NULL ) OR
            ( CodeNatureDispositif = @NumNatureDispositif )
          ) AND
 
          ( /* Ici tu listes tous les cas où tu n'as 
            pas besoin de filter sur ActionCorrectriceON... */
            ( @NumNatureDispositif In ( 1, 2, 5, 6 ) ) OR
            /* ... et donc si tu arrives là c'est que tu as 
               besoin de filter sur ActionCorrectriceON */
            ( TYPESDEFORMATION.ActionCorrectriceON = @ActionCorrectriceON )
          )
 
    ORDER BY [N°DOMAINE], DateDebutFormation
GO
 
-- Exemple d'appels :
-- cet appel effectuera tous les filtres
[dbo].[MappingMaster_ReferencesMois1] '01/01/2005', '31/01/2005', 15, 0
-- cet appel n'effectuera pas le filtre sur @ActionCorrectriceON
[dbo].[MappingMaster_ReferencesMois1] '01/01/2005', '31/01/2005', 5, 0
-- cet appel n'effectuera pas le filtre sur @PremiereDate
[dbo].[MappingMaster_ReferencesMois1] NULL, '31/01/2005', 1, 0
-- cet appel n'effectuera AUCUN filtre
[dbo].[MappingMaster_ReferencesMois1] NULL, NULL, NULL, NULL