Things started simply enough. In the first version of the now years-old ColdFusion application that Lindsay L maintains, all database queries were hard-coded.

<cfquery name="get_products" ds="#datasource#>
    select * from products where customer_id=#customer_id#
</cfquery>

This solution didn't scale as well as well as they'd hoped, however. As the schema changed and grew, more and more queries were breaking or becoming irrelevant.

Fortunately, somewhere along the line, someone hatched a plan to start moving all of the database queries into stored procedures. This couldn't happen overnight, however — no one had done any analysis or planning on which procedures were necessary, where there was duplicate logic, or which could be deleted. No, this would be a gradual change.

To avoid breaking existing functionality, all of the original code was left in. If a stored procedure existed to handle an existing query, they'd have a configurable switch to toggle stored procedures or hard-coded SQL. Someone created and ran a script to duplicate the queries for this change:

<cfif storedProcs>
  <cfquery name="get_products" ds="#datasource#>
    select * from products where customer_id=#customer_id#
  </cfquery>
<cfelse>
  <cfquery name="get_products" ds="#datasource#>
    select * from products where customer_id=#customer_id#
  </cfquery>
</cfif>

The storedProcs setting was enabled and the developers got to work, creating procedures for the most heavily used pages. One by one, the duplicated queries were replaced like so:

<cfif storedProcs>
  {call sp_get_products(#customer_id#)}
<cfelse>
  <cfquery name="get_products" ds="#datasource#>
    select * from products where customer_id=#customer_id#
  </cfquery>
</cfif>

And so it went... for maybe a dozen pages. With other bugs and priorities, and a system that generally worked, the initiative lost steam and was all but abandoned.

Months later, there was no indication that they'd ever pick the project back up, and newer team members didn't even know why most of the procedures were defined twice in the first place. Eventually, people had learned to accept and ignore the extra code. And since the storedProcs switch was always and would forever be on, most developers only maintained the truth side.

<cfif storedProcs>
  <cfquery name="get_products" ds="#datasource#>
    select * from products where
    customer_id=#customer_id#
    and status = 1
  </cfquery>
<cfelse>
  <cfquery name="get_products" ds="#datasource#>
    select * from products where customer_id=#customer_id#
  </cfquery>
</cfif>

Let's hope they never turn storedProcs off.

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