CASE dans un WHERE avec un IN()

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:

---

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

Citation:

---

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

Tu peux essayer cette requête :

Code:

---

1 2 3

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

---

A+

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

Code:

---

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

Pour faire le :

Code:

---

WHERE CHAMPID IN ( @Param )

---

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

Code:

---

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

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

Citation:

---

Envoyé par seb.49

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)

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

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

Code:

---

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:

---

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+