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

Question

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

Answer 1

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)