i got a table
id int idenetity(1,1)
col1 varchar(10) null
col2 varchar(10) null
i am sending a param to SP with values 0 1 or 2
if 0 - > i will fetch all rows where col1 is not null and col2 is not null
if 1 - > i will fetch all rows where col1 is not
if 2 - > i will fetch all rows where col2 is not
how can i build a where clause for this?
WHERE (@param = 2 OR col1 IS NOT NULL) AND
(@param = 1 OR col2 IS NOT NULL)
in this case if pass 1 it will return
also the value based on condition
(@param = 2 OR col1 IS NOT NULL)
which should be ignored
That's exactly what you said you wanted to do if the value is 1.
i solved it
where....
AND (@param =0) OR (@param = 2 AND Email IS NOT NULL) OR
(@param = 1 AND Cellphone IS NOT NULL)