L' Eomot Inversé (1/10/2012)

---

Jeff Moden (1/9/2012)

---

As a side bar, you've got a huge amount of unnecessary code in your code example that you posted. Take the following snippet, for example...

WHERE GETDATE() BETWEEN eff_date AND term_date

AND ( @CompanyID IS NULL

OR ( @CompanyID IS NOT NULL

AND @CompanyID > 0

AND company_id = @CompanyID

)

)

Because NULLs cannot be related to anything else including other NULLs and assuming that you're not actually using negative CompanyIDs, the code can be simplified quite a bit as follows...

WHERE GETDATE() BETWEEN eff_date AND term_date

AND (@CompanyID IS NULL OR company_id = NULLIF(@CompanyID,0))

I can't see any declarations anywhere in any of the code posted that prevents companyID being negative, and unless there is such a restriction this simplification is broken. It seems likely that there is such a restriction (column names with ID that get compared with 0 are often identity columns with 1 as initial seed) but there isn't certain to be, unless I've missed something.

...and assuming that you're not actually using negative CompanyIDs...