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 of1
. This would help in finding the unique row:SELECT ID FROM [Status] WHERE [IsAcknowledged] = 1)