When the Web Applications Group and the Distributed Applications Group were merged into one, it should have been a joyous day for Greg Beech. After all, Greg would finally get the chance to see first hand the "Enterprise CMS" system that the Distributed group had been bragging about for so many years. It was built by a team of elite consultants and billed "the quintessential collection of best practices."

After downloading the source code, following the seventy-two steps required to install the Enterprise CMS on a development workstation, and spending an entire afternoon fiddling around with his computer's settings, Greg finally had the software running. But when he fired it up in up in debug mode through Visual Studio, he noticed something strange: a preposterous number of exceptions were being thrown, many of which were of type DivideByZeroException.

Many of the more enterprisey exceptions – such as DomainContextCertificateException – were to be expected, as he was only running the code locally. But the myriad DivideByZeroExceptions just didn’t make sense. After digging through layer after layer of “best practices code”, Greg finally discovered the source: a “best practice” that was prevalently used throughout countless stored procedures. Such as this one.

CREATE PROCEDURE members_getMember(
  @MemberId int,
  @FetchBlockedMember bit,
  @FetchInactiveMember bit
  ...snip...
  ) AS

BEGIN
   ...snip...

   DECLARE @ActDivisor int, @BlockDivisor int
   SET @ActDivisor = ISNULL(@FetchInactiveMember,0)
   SET @BlockDivisor = ISNULL(@FetchBlockedMember,0)

   SELECT 
     M.MemberId, FirstName, LastName, MemberStatus, PrivacyFlags, 
     ApprovalStatus, Gender, TimeZone, PostalCode, PostalCodeExtra, 
     M.CountryCode, EmailRecipientId, City, State, Latitude, Longitude, 
     Active, Birthdate, RegisterDate, M.CreateDate, 
     M.PreferredCultureName, CodeName, NamespaceId

   FROM 
     Member M 
   INNER JOIN 
     MemberSourceCode S ON S.SourceCodeId = M.SourceCodeId
   LEFT JOIN 
     PostalCode Z on M.PostalCodeId = Z.PostalCodeId 
     and M.CountryCode = Z.CountryCode

   WHERE
     M.MemberId = @MemberId
     -- this causes errors for non-active members when you didn''t ask for them.
     AND (Active = 1 OR 1/(Active+@ActDivisor) IS NULL)
     -- this causes errors for blocked members when you didn''t ask for them.
     AND (ISNULL(ApprovalStatus,1) = 1 OR 
          1/(ISNULL(ApprovalStatus,1)+@BlockDivisor) > 0)

   ...snip...

END

Greg explains...

"You'll notice that the procedure takes parameters to indicate whether to retrieve active or blocked members. Now you or I might select the member details, check whether they are active or blocked, and then either return zero rows or raise an appropriate error using RAISERROR.

"The consultants, however, found a more innovative approach. Taking advantage of the fact that the BIT data type is not a true Boolean but is in fact the values 1 or 0, they add the value of the active/blocked flags to the parameter values and then divide by it, causing a 'divide by zero exception' if both the parameter and the flag are false.

Innovative, indeed.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!