I want to (loosely) have a stored procedure like
select * from table where col1 like @var
the default value of @var is '%', so if I don't have a value for @var it should return every row. However, the problem is it doesn't return rows where col1 is null.
How do I return all rows if @var = '%' and rows that are like @var if it does have a value?
[the actual sp is significantly more complex so I don't want to just wrap it in a if..then and have two selects]
select * from table where isnull(col1, '') like @var
Should do the trick.
Just a heads up more than anything. If this SP is to be used frequently and the data you are selecting from is large make sure you performance test your query (using either of the solutions above). From past experience using LIKE and ISNULL or IS NULL together can have a big performance hit
You could special case when @var is '%' to include all the null values like this:
select * from table where col like @var or (@var = '%' and col is null)
