Using parameters in stored procedures where the column may be null

I want to (loosely) have a stored procedure like

select * from table where col1 like @var

the d开发者_StackOverflow中文版efault 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)