Jorgen V Jorgen V - 1 year ago 143
SQL Question

Why using -1 in SQL

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)

Answer Source

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 NULL:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download