CODEWITHSUNDEEP
sqlsql-server
user788448
user788448

Reputation: 797

if condition in where statement

How do I write a query that if one column value is not null then compare the value with the querystring, else do not compare the column value.

for example, I have a tbl1 with programs1 and programs2 two columns, the value in these two columns can be null or with value. I want to have the if statement that say

if programs1 is not null, programs1=@programs  and 
if programs2 is not null, programs2=@programs

my query is like this: 

select * from tbl1 where
(programs1 is not null and programs1=@programs)
and 
(program2 is not null and programs2=@programs)

but it didn't work the way I want. How do I write a correct query for this case? TIA.

Upvotes: 0

Views: 216

Answers (3)

mayor
mayor

Reputation: 641

select *
from   tbl1
where  (programs1=@programs or programs1 is null)
  and  (programs2=@programs or programs2 is null)

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101614

I believe you're looking for COALESCE.

WHERE COALESCE(programs1, programs2) = @Programs

However, if you'd like to compare only if it has a value:

-- If the column is null, ignore it. If it has a value, then
-- check that value.
WHERE (programs1 IS NULL OR programs1 = @Programs)
  AND (programs2 IS NULL OR programs2 = @Programs)

Also, I assume you mean since you reference

Upvotes: 7

Sam Sussman
Sam Sussman

Reputation: 1045

ISNULL(programs1,programs2) = @Programs

or

(programs1 is not null and @Programs = programs1) or (programs2 is not null and @Programs = programs2)

Upvotes: 0

Related Questions