procédures stockées dynamiques

Version imprimable

28/12/2005, 18h04
Chucky69

procédures stockées dynamiques
Bonjour,

Je travaille avec une base sql server 2000.

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

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

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.

Code:

1 2 3 4 5 6 7 CREATE PROCEDURE [dbo].[MaProc] ( @StringSql3 ntext ) AS EXEC(@StringSql3)
Bien que ca soit une mauvaise idée!

29/12/2005, 11h38
Chucky69

Merci inoussa pour ta réponse,

Pourquoi une mauvaise idée...problème de performance ?
29/12/2005, 12h21
inoussa

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

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:

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:

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 !!! :help:

29/12/2005, 19h14
inoussa

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?
30/12/2005, 09h38
Chucky69

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).

Citation:

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

Code:

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:

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

sinon tu lui passe NULL

Code:

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

30/12/2005, 12h41
Chucky69

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:

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

ou

Code:

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

L'assistant me donne toujours une erreur de syntaxe.

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]" :

Citation:

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:

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

30/12/2005, 16h25
Chucky69

Merci pour toutes ces infos et ces conseils inoussa

Bonnes fêtes de fin d'année à toi
30/12/2005, 18h51
inoussa

bonne fete egalement.