Fetch row based on params and columns values

Transact-SQL
1

i got a table :slight_smile:
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?

2 
WHERE (@param = 2 OR col1 IS NOT NULL) AND
    (@param = 1 OR col2 IS NOT NULL)
3

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

4

That's exactly what you said you wanted to do if the value is 1.

5

i solved it
where....
AND (@param =0) OR (@param = 2 AND Email IS NOT NULL) OR
(@param = 1 AND Cellphone IS NOT NULL)