tsql - SQL: avoiding hard-coding or magic numbers

Question

Question: What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures?

Consider a stored procedure whose job is to check/update a value of a record based on its StatusID or some other FK lookup table or range of values.

Consider a Status table where the ID is most important, as it's a FK to another table:

The SQL scripts that are to be avoided are something like:

DECLARE  @ACKNOWLEDGED tinyint

SELECT  @ACKNOWLEDGED = 3   --hardcoded BAD

UPDATE  SomeTable
SET     CurrentStatusID = @ACKNOWLEDGED
WHERE   ID = @SomeID

The problem here is that this is not portable and is explicitly dependent on the hard-coded value. Subtle defects exist when deploying this to another environment with identity inserts off.

Also trying to avoid a SELECT based on the text description/name of the status:

UPDATE  SomeTable
SET     CurrentStatusID = (SELECT ID FROM [Status] WHERE [Name] = 'Acknowledged')
WHERE   ID = @SomeID

Question: What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures?

Some other thoughts on how to achieve this:

• add a new bit column (named like 'IsAcknowledged') and sets of rules where there can be only one row with a value of 1. This would help in finding the unique row: SELECT ID FROM [Status] WHERE [IsAcknowledged] = 1)

Answer 1

For situations like your status table, I create what I call "static" data sets. These tables contain data that

• Is set and defined upon creation,
• Never ever changes, and
• Is ALWAYS the same, from database instance to database instance, with no exceptions

That is, at the same time you create the table, you populate it as well, using a script to ensure that the values are always the same. Thereafter, no matter what where or when the database, you will know what the values are, and can hard-code accordingly and appropriately. (I would never use surrogate keys or the identity column property in these situations.)

You do not have to use numbers, you can use strings -- or binaries or dates, or whatever is simplest, easiest, and most appropriate. (When I can, I use char strings--and not varchars--such as "RCVD", "DLVR", ACKN", and so forth are easier hard-coded values than, say, 0, 2, and 3.)

This system works for non-extensible sets of values. If these values can be modified (such that 0 no longer means "acknowledged", then you have a security access problem. If you have a system where new codes can be added by users, then you have a different and tricky design issue to resolve.