Since I only do this once or twice a year, I always forget the trick. If you need to search on an id (or full text) and you want everything or just that id, use isnull, ex:
and s.SportID = isnull(@SportID,s.SportID)
It doesn’t seem to work as I’d like for a left outer join. You can put it in the left outer join “on” clause, but if you have an id and nothing matches it, you get everything–not the empty set I’d like to get. If you put it in the where clause and there is no id, you get nothing–even though I’d like to get everything.
Advertisement
