Discussion :

[seb.49]

Bonjour,

Dans une procédure stockée, je voudrais selon qu'un parametre est renseigné ou non que la clause WHERE s'adapte sachant que la clause WHERE contient un IN

J'ai trouvé une fonction qui transforme une chaine en une liste utilisable dans un IN.
L'adresse de la fonction est ici : http://www.devx.com/tips/Tip/20009
La fonction s'appelle dbo.UTILfn_Split

Voici la requete "à ma facon" qui ne marche pas mais qui se rapproche le plus du succès je crois.

Code :

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

1
2
3
4
5
6
7
8
 
SELECT * from MATABLE
WHERE 
CHAMPID IN (
Case 
          When 1=1 then ((select value from dbo.UTILfn_Split ('11,12', ',')))
          else CHAMPID
    end)

Le problème est le message

La sous-requête a retourné plusieurs valeurs. Cela n'est pas autorisé quand la sous-requête suit =, !=, <, <= , >, >= ou quand elle est utilisée en tant qu'expression.

Bref, je suis bloqué. Merci de votre aide

[hmira]

Tu peux essayer cette requête :

Code :

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

1
2
3
SELECT * 
FROM MATABLE
WHERE CHAMPID IN (SELECT value FROM dbo.UTILfn_Split ('11,12', ','))

A+

[seb.49]

Ce n'est pas suffisant.
Dans mon exemple (revu)

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT * FROM MATABLE
WHERE 
CHAMPID IN (
Case 
          When @Param then ((SELECT value FROM dbo.UTILfn_Split (@Param ',')))
          else CHAMPID
    end)      
--param étant une chaine de caractère passé à la procédure stockée

• Si le parametre est vide, alors je ne veux pas faire de where ou alors tout récupérer
• Le champ ID est un entier
• J'aurais bcp d'autre critère ensuite à ajouter

[pcaboche]

Pour faire le :

Code :

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

WHERE CHAMPID IN ( @Param )

on peut éventuellement exécuter une requête dynamique :

Code :

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

1
2
3
4
5
6
7
8
9
DECLARE @query VARCHAR(MAX) = 
  'SELECT * FROM MATABLE'
  +
  CASE WHEN @Param IS NOT NULL
  THEN ' WHERE CHAMPID IN (' + @Param +')'
  ELSE ''
  END
 
EXEC (@query)

C'est plus rapide que d'utiliser la fonction dbo.UTILfn_Split, mais c'est pas très propre.

Pour passer des listes d'arguments, j'aime bien utiliser une variable XML, par exemple (voir: OPENXML (T-SQL) ).

[seb.49]

Merci pour cette réponse,

En effet, la requete dynamique n'est pas propre et ca perd tout l'intérêt des procédures stockées a savoir la mise en cache du plan d'exécution.

Vous auriez un exemple du XML dans un IN? Merci

[pcaboche]

Vous auriez un exemple du XML dans un IN? Merci

Pas tout prêt sous la main, mais en gros :

• on déclare une table temporaire
• dans cette table temporaire on met le contenu de la variable XML (sp_xml_preparedocument, OPENXML, sp_xml_removedocument)
• on utilise la table temporaire dans une requête

Pour le deuxième point, il faut s'inspirer des exemples de la page OPENXML (T-SQL)

[hmira]

Tu n’étais pas assez précis dans ton premier post

Ci-dessous la requête répondant à ta demande

Code :

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

1
2
3
4
5
6
7
SELECT * 
FROM MATABLE
WHERE  ( (CHAMPID IN (SELECT cast(value as int) 
                      FROM dbo.UTILfn_Split (@Param, ',')) ) 
         OR (ISNULL(@Param,'') = '')
      )
-- AND .... (autres critères)

Remarque : En terme de performance, cela a été démontré, ce " bricolage" est beaucoup moins performant que la construction dynamique d’une requête.

Code :

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

EXEC sp_executesql ...

En effet, vouloir traiter tout les cas possibles dans une clause WHERE, l’optimiseur finit par établir un plan d’exécution moins performant, parfois même catastrophique avec des full scan sur les tables etc..
Conclusion : Dans ce genre de situation préférez l’utilisation du sql dynamique
EXEC sp_executesql

A+