SQL Bad Practice - Hardcoding

Legacy systems present design challenges different to problems faced during greenfield development. One mistake I've seen time and again at multiple organisations is the problem of hardcoding. I cringe when I view a stored procedure in SSMS covered with red text. I find it's often accompanied by an absence of annotation within the code.

SELECT
    FirstName + ' ' + LastName as "Full Name",
    case UserRole
        when 2 then "Admin"
        when 1 then "Moderator"
        else "User"
    end as "User's Role",
    case SignedIn
        when 0 then "Logged in"
        else "Logged out"
    end as "User signed in?",
    Convert(varchar(100), LastSignOn, 101) as "Last Sign On",
    DateDiff('d', LastSignOn, getDate()) as "Days since last sign on",
    AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' +
        City + ', ' + State + ' ' + Zip as "Address",
    'XXX-XX-' + Substring(
        Convert(varchar(9), SSN), 6, 4) as "Social Security #"
FROM Users

These are reasons why it's a bad practice:

• Concatenating columns for presentation purposes should be done at the application or report to which the data is returned.
• Extensive CASE statements classifying data is often a sign that a descriptive column is missing from the schema. I've seen CASE statements covering 2 pages of printed A4. 
• Hardcoding business logic into stored procedures leads to errors when the business logic changes. Alternatively it leads to more complexity being added to the hardcoding to cater for a slightly different case. Eventually, this path leads to code so complex that nobody dares to touch it.
• Use of sting functions, particularly scalar functions, can cause performance deterioration. I once inherited a database where the developer had constructed his own BETWEEN function and used it extensively. It mirrored the built-in BETWEEN function in it's output whilst performing terribly. Aaron Bertrand has an excellent blog post on why "Between is Evil".

So what can you do to eliminate this bad practice. Read my post of refactoring legacy systems.

If you can think of other reasons why excessive hardcoding is bad news, leave a comment.