CODEWITHSUNDEEP
sqlsql-server
LEM
LEM

Reputation: 862

How to check if a column is equal to a variable which can be null

I have a table that contains a varchar column that allows null or empty values.

I'm writing a stored procedure that contains a variable that can be assigned to a null value or a regular string (not empty)

It's defined like this:

declare @myvar varchar(50)

Now I'm trying to write a query that returns the rows where the column is equal to this variable or it is empty or null. So far I thought this was going to work:

select * from mytable where mycolumn =@myvar or mycolumn =''

However if the column is null and the variable is null it will not return any values.

I know I can make it work doing something like this:

select * from mytable where (mycolumn = @myvar and mycolum is not null) or mycolumn is null or mycolumn =''

Is there a better way of doing this?

PS: I would prefer not to do ANSI_NULLS OFF

Thanks

Upvotes: 2

Views: 16617

Answers (3)

wesm
wesm

Reputation: 451

If you want the column to exactly match the parameter (including null value)

DECLARE @myVariable varchar(10)

SELECT * 
FROM mytable 
WHERE ((@myVariable IS NULL AND myColumn IS NULL) OR myColumn = @myVariable)

Upvotes: 12

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

You're writing

select * from mytable where (mycolumn = @myvar and mycolum is not null) or 
   mycolumn is null or mycolumn =''

Since if mycolumn is NULL, it'll be true, you don't need to guard against it using and mycolum is not null, so it can be simplified to;

SELECT * FROM mytable 
WHERE mycolumn IS NULL OR mycolumn ='' OR mycolumn = @myvar

Upvotes: 2

Lamak
Lamak

Reputation: 70638

You don't need this part: and mycolum is not null since, as you said, if your variable is NULL it still won't match. So I think that this is actually the best way:

select * 
from mytable
where mycolumn = @myvar or mycolumn is null or mycolumn =''

If you want to do the same, but with less conditions, you could do:

select * 
from mytable
where mycolumn = @myvar or ISNULL(mycolumn,'') = ''

but this will not use an index (if there is one) on mycolumn.

Upvotes: 3

Related Questions