CodeSOD: Cast Away

The accountants at Gary's company had a problem: sometimes, when they wanted to check the price to ship a carton of product, that price was zero. No one had, as of yet, actually shipped product for free, but they needed to understand why certain cartons were showing up as having zero cost.

The table which tracks this, CartonFee, has three fields: ID, Carton, and Cost. Carton names are unique, and things like 12x3x6, or Box1, or even Large box. So, given a carton name, it should be pretty easy to update the cost, yes? The stored procedure which does this, spQuickBooks_UpdateCartonCost should be pretty simple.

ALTER PROCEDURE [dbo].[spQuickBooks_UpdateCartonCost]@Carton varchar(100),@Fee decimal(6,2)ASBEGINDECLARE @Cost decimal(8,3) = LEFT(CAST(CAST((CAST(@Fee AS NUMERIC(36,3))/140) * 100 AS NUMERIC(36,3)) AS VARCHAR), LEN(CAST(CAST((CAST(@Fee AS NUMERIC(36,3))/140) * 100 AS NUMERIC(36,3)) AS VARCHAR)) - 1) + CASE WHEN RIGHT(LEFT(CAST(CAST((CAST(@Fee AS NUMERIC(36,3))/140) * 100 AS NUMERIC(36,4)) AS VARCHAR),LEN(CAST(CAST((CAST(@Fee AS NUMERIC(36,3))/140) * 100 AS NUMERIC(36,4)) AS VARCHAR)) - 1), 1) > 5 THEN '5' ELSE '0' ENDIF NOT EXISTS (SELECT 1 FROM CartonFee WHERE Carton = @Carton) BEGININSERT INTO CartonFee VALUES (@Carton, @Cost)END ELSE BEGINUPDATE CartonFee SET Cost = @Cost WHERE Carton = @CartonENDEND

Just stare at that chain of casts for a moment. It teeters on the verge of making sense, calls to LEFT and RIGHT and multiplying by 100- we're just doing string munging to round off, that must be what's going on. If I count the parentheses, and really sit down and sketch this out, I can figure out what's going on, it must make sense, right?

And then you spot the /140. Divide by 140. Why? Why that very specific number? Is it a secret code? Is it a signal to the Illuminated Seers of Bavaria such that they know the stars are right and they may leave Aghartha to sit upon the Throne of the World? After all, 1 + 4 + 0 is five, and as we know, the law of fives is never wrong.

As it turns out, this stored procedure wasn't the problem. While it looks like it's responsible for updating the cost field, it's never actually called anywhere. It was, at one point, but it caused so much confusion that the users just started updating the table by hand. Somebody thought they'd get clever and use an UPDATE statement and messed up.

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!