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
Best Answer
If you want the column to exactly match the parameter (including null value)