T-SQL ISNULL() Optimization

Question

I have this clause on a join in a stored procedure I inherited:

WHERE a.is_active = 1
AND b.due_date <= ISNULL(@due_date_limit, b.due_date)

How would I re-write this to remove the ISNULL as it's causing severe performance problems?

Answer 1

in this instance, i would have an if statement, checking @due_date_limit

IF (@due_date_limit IS NULL)
BEGIN
    ...
    WHERE   a.is_active = 1 --not required to compare b.due_date <= b.due_date
END
ELSE
BEGIN
    ...
    WHERE   a.is_active = 1
    AND     b.due_date <= @due_date_limit
END