Discussion :

[marsup54]

Bonjour,

J'ai une procédure stockée qui retourne en sortie une requète SQL. En entrée, elle reçoit en argument des paramètres définis ci dessous :

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
 
CREATE PROCEDURE MaProcStock
(@Organisation varchar(1000), @Etat varchar(10))
AS
-- variables locales de la procédure
DECLARE @c_emploi   VARCHAR(8),
.............
BEGIN
-- ma requete ...
SELECT ... FROM ... WHERE maColonne IN (@Organisation)
................
 -- Traitement, insert dans une table temporaire
END
-- envoi des données
SELECT DISTINCT COL1, COL2,... FROM MATABLE ...
-- pas de messages intempestifs
SET NOCOUNT OFF
RETURN
-- gestion des erreurs
LBL_ERROR:
RAISERROR (@ErrMsg, 16, 1)

Ma variable @organisation reçoit une chaine de longueur indéfinie (fct de la sélection utilisateur) :

Code :

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

'54000','55400','69000', ...

De cette manière, l'on peut passer directement cette variable dans la requète :

Code :

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

SELECT ... FROM ... WHERE maColonne IN ('54000','55400','69000', ...)

Le problème est que lorsque j'appelle ma procédure stockée (via un outils décisionnels : BO, Harry Software, ...), j'ai un message d'erreur du genre "le nombre de paramètres passés en arguments est supérieur à la définition ...).

En fait, ma chaine est interprétée comme plusieurs paramètres (sûrement à cause des ",") ...
Quand mon organisation n'a qu'une seule valeur, tout fonctionne correctement.

J'ai bien essayé d'envoyer les données de la variable entre parenthèse, mais cela ne fonctionne pas !!!

Merci d'avance pour votre aide.

PS : le traitement de ma chaine en amont (remplacement des "," par autres choses n'est pas envisageable car cela provient d'une liste de valeurs générées par un outils décisionnel).

[marsup54]

Je me demandais justement s'il était possible pour répondre à mon problème de créer un curseur en SQL dynamique !!!

Y'a déjà eu un post sur ce sujet (pour DB2), mais ...
http://www.developpez.net/forums/showthread.php?t=35686

Merci d'avance pour votre aide

++

[NumaK]

Salut,

Je vois 2 solutions pour résoudre ton pb :

1 - construire la requête SQL et l'exécuter "dynamiquement"

Code :

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

1
2
3
4
5
6
7
8
9
 
-- Crée la table temporaire : @tbl_Temp
...
-- Sélectionne les lignes dans la table temporaire
SET @query = 'SELECT ... INTO @tbl_Temp FROM ... WHERE maColonne IN '+ @Organisation
EXEC(@query)
 
-- Traite les données de la table temporaire
...

2 - Utiliser une fonction qui parse le param @organisation et renvoie un curseur et faire une jointure sur ta table de données

Code :

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

1
2
3
 
-- La f° fn_StrToken renvoie un curseur avec une colonne [Token]
SELECT ... INTO @tbl_Temp FROM ... INNER JOIN dbo.fn_StrToken(@organisation) ON ... = Token

Si la 2è solution t'intéresse, je peux te donner le source d'une f° que j'utilise.

[marsup54]

La 2ème solution m'intérresse en effet !

Il faut que je te contacte par MP ?

Encore merci pour ton aide !

PS : je vais également tester ta première méthode, mais je pense que cela ne fonctionnera pas car la procédure interpretera les n valeurs de @organisation comme n paramètres en entrée.
J'éditerai une fois les tests réalisés

++

[NumaK]

Non, pas de MP voyons ... quand on partage, c'est pour tout le monde

Voici le code de la f°

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
CREATE FUNCTION fn_StrToken
(
  @p_Str        VARCHAR(4000),
  @p_Separator  CHAR(1)
)
RETURNS
  @r_Tokens TABLE
  (
    Token VARCHAR(255)
  )
AS
BEGIN
  DECLARE
    @l_Pos    INT,
    @l_Token  VARCHAR(255)
 
  -- Supprime les espaces à gauche et à droite
  SET @p_Str = LTRIM(@p_Str)
  SET @p_Str = RTRIM(@p_Str)
 
  IF( LEN(@p_Str) = 0 )
    RETURN
 
lbl_Start:
  -- Recherche la première occurence du séparateur
  SET @l_Pos = CHARINDEX(@p_Separator, @p_Str)
 
  -- Séparateur trouvé
  IF( @l_Pos > 0 )
  BEGIN
    SET @l_Token = LEFT(@p_Str, @l_Pos - 1)
 
    -- Enregistre le token
    INSERT INTO @r_Tokens
      SELECT @l_Token
 
    -- Supprime le token de la chaîne source
    SET @p_Str = RIGHT(@p_Str, LEN(@p_Str) - @l_Pos)
 
    -- Passe au token suivant
    GOTO lbl_Start
  END
 
  -- Renvoie la chaîne restante comme token
  IF LEN(@p_Str) > 0
    INSERT INTO @r_Tokens
      SELECT @p_Str
 
  RETURN
END

Pour l'utiliser, il suffit de faire

Code :

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

SELECT * FROM dbo.fn_StrToken('1,2,3')

J'ai un peu épuré la fonction qui gérait également la longueur mini d'un token, la possibilité d'avoir des tokens en double, ...

Si ça t'intéresse

[NumaK]

Au fait, j'ai oublié ...

La procédure n'interprétera pas le paramètre @organisation comme des params d'entrée ... c'est une chaîne de caractères et - heureusement - elle ne fait aucune analyse de ce genre.

Par contre, il y une ch'tite erreur dans mon code; j'ai oublié les parenthèses

Code :

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

SET @query = 'SELECT ... INTO @tbl_Temp FROM ... WHERE maColonne IN ('+ @Organisation +')'

[marsup54]

OK merci !

Je teste ça demain car suis en réunion toute la journée ...
Et oui, je surf en même temps !

++

PS : J'éditerai ce post pour faire part de mes résultats !

[NumaK]

Donc, intéressante la réunion ...

[rudib]

pour info, si tu avais plus de contrôle sur ta chaîne d'entrée, un bon truc est d'envoyer ton tableau en format XML dans un param TEXT par exemple, et de la récupérer directement avec un OPENXML en SQL 2000, ou directement dans un param de type XML en SQL Server 2005, pour l'utiliser dans un JOIN.

[marsup54]

pour info, si tu avais plus de contrôle sur ta chaîne d'entrée, un bon truc est d'envoyer ton tableau en format XML dans un param TEXT par exemple, et de la récupérer directement avec un OPENXML en SQL 2000, ou directement dans un param de type XML en SQL Server 2005, pour l'utiliser dans un JOIN.

Bonne idée effectivement, mais dans mon cas, c'est un outil décisionnel qui va lancer la procédure stockée qui va me générer un tableau de données que l'on renvoie vers un report !!!
Donc, je n'ai pas trop la main sur la liste de valeurs envoyée en argument.

++

EDIT :
En entête de ma procédure, je déclare comment mes paramètres d'entrée ?
Notamment pour la variable @organisation !
Je suppose, qu'il faut mettre ma variable @Etat en première position et ensuite @organisation de manière à ne pas avoir de limite dans les paramètres ...
Merci d'avance pour vos réponses.

[marsup54]

Après quelques tests, cela semble fonctionner correctement !

J'attends de terminer mes investiogations pour mettre le tag "Résolu".

Encore merci !

++