This just shows how people will overly complicate a set-oriented or SQL based solution because they are trying to fit it into a world they are more comfortable with.
And this article is just a shameless plug to go to the author's site to download his cool stuff.
In compiled-programming we use constants so we don't have the magic #100 spread throughout the code. When the "max-cartons-per-palette" rule changes to 150, we don't want to find all the cases of "100" and try to figure out whether it was *that* 100 or some other 100 in order to safely change it to 150.
Under the hood, the compiler turns "MAX_CARTONS" into the literal 100 for when the program actually executes.
In SQL and table-oriented designs, there are 2 major differences.
First, the code is interpreted (generally) and not compiled. Other posters have already pointed out the pitfalls with some of these "functions-for-constants" approach.
Second, and more importantly, this is precisely what lookup tables were designed for-- to assign a simple constant to something bigger that could change, like a status-code-description.
Far and away the most common "constant" situation I encounter are things like "status code 3 means 'failed'". So people start coding around it in all sorts of convoluted ways because they come from a programming background where they were taught that hard-coding a "3" in their code is EVIL and they will go to programmer hell for it. I saw one case where the programmer was doing:
select ...
from ...
where StatusCode = (select StatusCode from LookupTable where description = 'failed')
Yikes!
The problem is-- it's not the NUMBER that will change. It's the other parts. Anything *except* the number might change. "Failed" might become "Failure" or "Didn't Work". But in good lookup table design, the 3 will remain always and forever the surrogate to identify the underlying concept of a failed state.
The lookup table provides a constant, never changing source of this fact and is always available for reference if needed. The 3 will never change.
It would be NICE if T-SQL allowed more structured programming, like defining other modules that you can "include" and such. Basically if it were compiled. But for now it's not. And workarounds like this article proposes just make it more complex without adding value.
The best solution I've seen is simply to work-around T-SQL's language by declaring constants at the top of a procedure:
declare @STATUS_FAILED int; set @STATUS_FAILED = 3;
... (later in code) ...
select field
from table
where Status = @STATUS_FAILED;
... Then use that variable (upper-case to make visually distinct from ones that can change during the procedure) in the rest of the code.
We don't have to worry about ever changing the 3 to mean something else, so it isn't a case where having some named constant makes maintenance easier.
An almost-as-good alternate is to just hard-code the numeric with a comment:
SELECT ....
FROM MyTable
WHERE Status = 3 /*failed*/
Remember, for all the cases driven by lookup tables, it's the *number* (or whatever PK you used) that doesn't change.
For other numeric constants like Int-Upper-Bound or Pi or something, I agree with the other poster about using a one-row table. I personally use an instead-of upd/del trigger to disallow changes to that table except for official rollout scripts we publish. But same idea.
To the author: By the way, please go back and read more on relational. Not only because of the nonsense about constants, but to also understand why identifying them by "Western" or "Region.Western" has nothing to do with relational theory at all. Start with Chris Date's stuff.