run one where clause condition on the base of parameter

Question

i have ha query like this

Select * from table1 
where col1 = true or col2 = false or 
case when @param <> 2 then col3 = col4 end

last condition in where is throwing an error

 Incorrect syntax near '<'

You can refer this answer if you really want to use `case` in `where` clause https://stackoverflow.com/a/206500/4923740 — an33sh, Jul 06 '17 at 07:03

score 3 · Answer 1 · answered Jul 06 '17 at 06:56

You cannot use a CASE expression to control execution flow in SQL. CASE is an expression that simply returns a scalar value.

You can use the following predicates instead:

((@param <> 2 AND col3 = col4) OR (@param = 2))

So, the WHERE clause of your query will look like:

WHERE (col1 = true) OR (col2 = false) OR ((@param <> 2 AND col3 = col4) OR (@param = 2))

score 2 · Accepted Answer · answered Jul 06 '17 at 06:56

2

Try like below

SELECT *
FROM TABLE1
WHERE ( COL1='TRUE' OR COL2='FALSE' 
OR (@PARAM<>2 AND COL3=COL4) )

answered Jul 06 '17 at 06:56

Shakeer Mirza

5,054
2
18
41

`(@PARAM<>2 AND COL3=COL4) )` gud one bro . but can't we handle this with case statement as well as you told me previous before ..?? – Mahesh.K Jul 27 '17 at 11:21
1

You can handle with `CASE` too. Case expression won't use indexes exist on that column. But the above approach will use indexes. If there is no index available on those columns both approaches will give same performance. @Mahesh.K – Shakeer Mirza Jul 27 '17 at 11:26

score 0 · Answer 3 · answered Jul 06 '17 at 07:24

I assume case in question is intended to express predicate "when @param <> 2 then col3 = col4 must hold". I also assume for simplicity no column is nullable. This quite formally translates into !(@param <> 2) OR col3 = col4 which is equivalent to @param = 2 OR col3 = col4

Select * from table1 
where col1 = 'true' or col2 = 'false' or 
@param = 2 OR col3 = col4

run one where clause condition on the base of parameter

3 Answers3