[SQL SERV 2K]Proc stock : passage d'une longue chaine en paramètre d'entrée

Version imprimable

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:

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:

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

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

Code:

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

15/05/2006, 18h35
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

++

Salut,

Je vois 2 solutions pour résoudre ton pb :

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

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:

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.

16/05/2006, 14h02
marsup54

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

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

++

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

Voici le code de la f°

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

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

16/05/2006, 14h25
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:

SET @query = 'SELECT ... INTO @tbl_Temp FROM ... WHERE maColonne IN ('+ @Organisation +')'
16/05/2006, 15h19
marsup54

OK merci !

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

++

PS : J'éditerai ce post pour faire part de mes résultats !
16/05/2006, 15h21
NumaK

Donc, intéressante la réunion ... :)
16/05/2006, 15h39
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.
16/05/2006, 16h21
marsup54

Citation:

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.
17/05/2006, 11h50
marsup54

Après quelques tests, cela semble fonctionner correctement ! :D

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

Encore merci !

++