Sql server case statement in where clause

Question

select * from table1 t1 where t1.active=1 and case when @x=1 then t1.name="" else t1.name "". please help to write it correct syntax

Sachin Singh · Answer

first please clearify , do you want to select * or Just name as far as i Could understand you want to select * where status is 1 but either x=1 and name is null or x!=null and name is null then what's wrong in the below query create procedure spGet @x int = null as begin select * from table1 t1 WHERE t1.active=1 and((@X=1 and t1.name is null ) OR (@X 1 and t1.Name is not null ) ) end

Sachin Singh · Answer

your query is simple why are you making it complicated select * from table1 t1 WHERE (t1.active=1 and t1.name is null ) OR (t1.active 1 and t1.Name is not null )

P K · Answer

Need all row having name column have some value if x=1 else all row having name value empty and status active

P K · Answer

this will return same result in both case, need if x=1 then only name that is not empty else where name value is empty

P K · Answer

there need to add condition based on value of @x paramter, please see @X

P K · Answer

Checked it but need conditional exp. as same as posted, please check it

Mageshwaran R · Answer

Hi Com, Please try this code: SELECT * FROM Customer WHERE Salary=20000 CASE WHEN First_Name = ‘RAM’ THEN Salary WHEN Last_Name = ‘SHARMA’ THEN Salary ELSE First_Name END

Sql server case statement in where clause

Answers (7)