So I was looking at a query made by a former employee and I noticed that he used -1 in the WHERE clause. I've tried searching online to find an explanation what the -1 does but I can't find an answer. Can anyone explain me what the function of this -1 is?
@EmployeeId = -1 OR
tblReg.PurchaseOrderId IN ( SELECT DISTINCT r2.PurchaseOrderId
FROM Registration r2
INNER JOIN PurchaseOrder p2 ON p2.PurchaseOrderId = r2.PurchaseOrderId
WHERE p2.statuscid = 1 AND r2.IsBillable = 1 AND r2.SetNonBillable = 0 AND r2.EmployeeId = @EmployeeId
AND r2.RegistrationDate < @EndDate AND r2.RegistrationDate >= @StartDate)
This type of construct is probably used to mean "get all employees". That is, when the variable
@EmployeeId has the value
-1, then all employees are returned.
The more typical way of handling this is with
where (@EmployeeId is null) or . . .
Your colleague's version assumes (probably safely) that the
id is never negative. However, it is always possible that someone will come along and invent some new meaning for the id. A "fabulous idea" like "Let's give former employees a negative
EmployeeId value" would break this -- and perhaps much other code.