Best pattern for Constants in SQL?

I have seen several patterns used to 'overcome' the lack of constants in SQL Server, but none of them seem to satisfy both performance and readability / maintainability concerns.

In the below example, assuming that we have an integral 'status' classification on our table, the options seem to be:

• Just to hard code it, and possibly just 'comment' the status

-- StatusId 87 = Loaded
SELECT ... FROM [Table] WHERE StatusId = 87;

• Using a lookup table for states, and then joining to this table so that the WHERE clause references the friendly name.

SubQuery:

SELECT ... 
FROM [Table] 
WHERE 
  StatusId = (SELECT StatusId FROM TableStatus WHERE StatusName = 'Loaded');

or joined

SELECT ... 
FROM [Table] t INNER JOIN TableStatus ts On t.StatusId = ts.StatusId 
WHERE ts.StatusName = 'Loaded';

• A bunch of scalar UDF's defined which return constants, viz

CREATE Function LoadedStatus()
RETURNS INT
AS
 BEGIN
  RETURN 87
 END;

and then

SELECT ... FROM [Table] WHERE StatusId = LoadedStatus();

(IMO this causes a lot of pollution in the database - this might be OK in an Oracle package wrapper)

• And similar patterns with Table Valued Functions holding the constants with values as rows or columns, which are CROSS APPLIED back to [Table]

How have other SO users have solved this common issue?

Edit : Bounty - Does anyone have a best practice method for maintaining $(variables) in DBProj DDL / Schema scripts as per Remus answer and comment?