Hard-coding Values In SQL Code – Please Don’t

What is hard-coding?

Wikipedia defines it as below

Hard coding (also, hard-coding or hardcoding) refers to the software development practice of embedding what may, perhaps only in retrospect, be regarded as input or configuration data directly into the source code of a program or other executable object, or fixed formatting of the data, instead of obtaining that data from external sources or generating data or formatting in the program itself with the given input.

More importantly, it goes on to say the following:

Considered an anti-pattern, hard coding requires the program’s source code to be changed any time the input data or desired format changes, when it might be more convenient to the end user to change the detail by some means outside the program

What is it in SQL terms?

Let us think about the above statement for a minute. In SQL terms, it is referring to something like this, where specific set of identifying numbers/strings are placed in source code (not adhoc queries) and it could have been done differently.

      CASE WHEN CompanyID IN (7793,5280,7790,7792,7794,2974,7789,7791,3120)
               THEN 'SMALL'
            ELSE 'LARGE'
      END AS SalesSize

Silly arguments for hard-coding

I have heard arguments for doing this type source code

• This is a one-time thing. We do not have the need to do it anywhere else
• We are on a deadline
• We do not have the ability to test if this was not done this way
• My program is going away in a week
• We do not have the time to correct this
• I am just following the existing pattern
• Unofficially (not) said – “This is my job security”

Why the arguments are really silly?

If you reflect back on each of these over the years, none of them turned out to be true.

• This is a one-time thing. We do not have the need to do it anywhere else
  • When it starts, it was in one place. As time goes on, it is everywhere; not to mention that it is slightly different everywhere and it should not be!
• We are on a deadline
  • You met your deadline but code does not work as intended. Making changes to your code is so much more difficult now because a new developer who just joined, has no idea how many places these magic numbers exist at.
• We do not have the ability to test if this was not done this way
  • Would you rather test by changing a single lookup table data (or) all the source code which has this reference? Without hardcoding, once tested, everything remains tested.
• My program is going away in a week
  • Do you recall the last time your program really did go away?
• We do not have the time to correct this
  • You are setting yourself up to not only correct this in the future but also explain why the results suddenly went bad after an incomplete change. Then, you certainly will not have the time to fix the data problems that arose because the new developer forgot to update all the places these magic numbers existed at. This will happen at the exact time you do not want this sort of thing to happen.
• I am just following the existing pattern
  • If someone shot himself/herself in the foot, would you follow that pattern?
• Unofficially said – “This is my job security”
  • Yes, it somewhat is, at the cost of making your own life a hell. Here is some funny advice for you. In fact, you might be missing out on a whole lot of more advice on “how to ensure job for life”.

Hard-coded Example

 I am going to show how easy it is to move away from hard-coding in a scenario like the one shown above.

Let us say I have a query as shown below that loops through a specific set of company_id’s to produce a quarterly report (Oracle code but it is quite similar for SQL Server).

FOR v_Rec IN (
    SELECT BusinessEntity
    FROM CompanyTable
    --We only need to produce reports for these companies
    WHERE CompanyID IN (7793,5280,7790,7792,7794,2974,7789,7791,3120)
            ) LOOP

    --Do something else for each BusinessEntity

    --More logic with references to 7793,5280,7790,7792,7794,2974,7789,7791,3120
END LOOP;

The simple fix! 

I am trying to show a simple way but you can get as fancy as you want with lookup tables. Sometimes, people take these to the other end of the hardcoding spectrum.

Create a lookup table:

CREATE TABLE quarterly_report_companies(
    Company_Id NUMBER,
    Create_UserId VARCHAR2(50) NOT NULL,
    Create_DateTime DATE NOT NULL,
    Update_UserId VARCHAR2(50) NOT NULL,
    Update_DateTime DATE NOT NULL
);

Insert your magic numbers into this table

INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (7793, USER, SYSDATE, USER, SYSDATE);
INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (5280, USER, SYSDATE, USER, SYSDATE);
INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (7790, USER, SYSDATE, USER, SYSDATE);
INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (7792, USER, SYSDATE, USER, SYSDATE);
INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (7794, USER, SYSDATE, USER, SYSDATE);
INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (2974, USER, SYSDATE, USER, SYSDATE);
INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (7789, USER, SYSDATE, USER, SYSDATE);
INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (7791, USER, SYSDATE, USER, SYSDATE);
INSERT INTO QuarterlyReportCompanies (CompanyID, Create_UserId, Create_DateTime, Update_UserId, Update_DateTime)
    VALUES (3120, USER, SYSDATE, USER, SYSDATE);

COMMIT;

Change your hard-coded references to use the lookup table instead:

FOR v_Rec IN (
                SELECT BusinessEntity
                FROM CompanyTable ct
                --We only need to produce reports for companies in QuarterlyReportCompanies table
                WHERE EXISTS (
                        SELECT 1
                        FROM QuarterlyReportCompanies qrc
                        WHERE ct.CompanyID = qrc.CompanyID
                        )
            )

        --Do something else for each BusinessEntity

END LOOP;

Done! Yes, it is actually that simple.

Now, if there is a change in the list, you would change the table data and you are done. You do not have to touch source code when you don’t have to.

If you have additional attributes by which to produce the report, you could add additional columns to the lookup table. Developers who hard-code not only impede the maintenance but also the company’s expansion plans because it cannot happen with a few quick updates as the senior management believes it ought to be!

Advertisement