CODEWITHSUNDEEP
sqlsql-server
Hong Van Vit
Hong Van Vit

Reputation: 2986

how can put if condition in where clause

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 + '%')

Upvotes: 1

Views: 296

Answers (3)

dwir182
dwir182

Reputation: 1549

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

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10711

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.

Upvotes: 2

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

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.

Upvotes: 3

Related Questions