CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Brainiac
Brainiac

Reputation: 107

run one where clause condition on the base of parameter

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

Upvotes: 1

Views: 221

Answers (3)

Serg
Serg

Reputation: 22811

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

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Try like below

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

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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

Upvotes: 3

Related Questions