Sql server case statement in where clause

P K

5y866

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted 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
P K5y ago
Need all row having name column have some value if x=1 else all row having name value empty and status active
P K5y ago
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 K5y ago
there need to add condition based on value of @x paramter, please see @X
Sachin Singh5y ago
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 K5y ago
Checked it but need conditional exp. as same as posted, please check it
Mageshwaran R5y ago
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