Re: Optional Where Parameters on Null Data

BillCo wrote:

I have a procedure that I use to return data based on optional
parameters. It works fine, except when the underlying data contains a
null on one if the fields being searched.

My system uses a default wildcard for all parameters, so this excludes
such records. I need a way to add in " OR fldName IS NULL " where the
parameter is empty or '%'. I've looked at using CASE WHEN, but it
doesnt seem to like SQL Keywords being part of the WHEN clause.

Try this:

WHERE COALESCE(tblUnits.strUnitID,'') LIKE @strUnitID
etc.
.