1

Please don't put duplicate with question IF clause within WHERE clause ? I think my question is difference, I have been tryed but I can't not run success with condition below.

How can put if condition between where in sql for example

        SELECT *
        FROM LocationOutsw W
        WHERE if(@p_type = 'ALL') then
            (W.NAME LIKE '%'+ @p_search + '%' OR W.DESCRIPTION LIKE '%'+ @p_search + '%')
            if(@p_type == "NAME")
              W.NAME LIKE '%'+ @p_search + '%'
            if(@p_type == "DESCRIPTION ")
              W.DESCRIPTION LIKE '%'+ @p_search + '%')
Ilyes
  • 14,640
  • 4
  • 29
  • 55
Hong Van Vit
  • 2,884
  • 3
  • 18
  • 43

3 Answers3

3
SELECT *
FROM LocationOutsw W
WHERE ( @p_type = 'ALL'
    AND ( W.NAME LIKE '%'+ @p_search + '%'
            OR W.DESCRIPTION LIKE '%'+ @p_search + '%'
        )       
) 
OR (    @p_type = 'NAME'
    AND W.NAME LIKE '%'+ @p_search + '%'
)
OR ( @p_type = 'DESCRIPTION'
    AND W.DESCRIPTION LIKE '%'+ @p_search + '%'
)

But I strongly recommend not to write your queries like so, because of wrong query plans you will get.

Denis Rubashkin
  • 2,151
  • 1
  • 9
  • 14
2

I would use boolean expressions for this

    SELECT *
    FROM LocationOutsw W
    WHERE (@p_type = 'ALL' and W.NAME LIKE '%'+ @p_search + '%' OR W.DESCRIPTION LIKE '%'+ @p_search + '%')
        or (@p_type = 'NAME' and W.NAME LIKE '%'+ @p_search + '%') 
        or (@p_type = 'DESCRIPTION' and W.DESCRIPTION LIKE '%'+ @p_search + '%')

Be careful in syntax. Apostrophes have a different meaning than quotation mark.

Radim Bača
  • 10,646
  • 1
  • 19
  • 33
2

I would recommended this way.. It will do better..

SELECT 
  *
FROM 
  LocationOutsw W
WHERE 
   (@p_type = 'ALL' AND
   (W.NAME LIKE '%'+ @p_search + '%'  OR  W.DESCRIPTION LIKE '%'+ @p_search + '%'))
   OR
   (@p_type = 'NAME' AND W.NAME LIKE '%'+ @p_search + '%')
   OR
   (@p_type = 'DESCRIPTION' AND W.DESCRIPTION LIKE '%'+ @p_search + '%')

And ' Single quote for String not " Double Quote..

dwir182
  • 1,539
  • 10
  • 20