declare @Dates table (aDate date) insert into @Dates values ('1/1/2000'); insert into @Dates values ('1/1/2001'); insert into @Dates values ('1/1/2002'); insert into @Dates values ('1/1/2003'); declare @AnyDateFound bit = 0; declare @MaxDate date; select @AnyDateFound = 1 from @Dates where aDate > '1/1/2005';
In this example, @AnyDateFound would obviously be 0.
I made an update like this:
declare @MaxDate date; select @AnyDateFound = 1, @MaxDate = max(aDate) from @Dates where aDate > '1/1/2005';
Most SQL Developers would spot the error right away. But I assumed that if no records met the conditions in the where clause, @AnyDateFound would not be updated to 1, and would remain 0. Not the case.
The problem is that aggregates always return records. There is a maximum, even if the maximum is null. It makes perfect sense when you think about.
select 0 where 1 = 2; -- no records select max(0) where 1 = 2; -- one record