Magic Numbers in Code: Replacing Status Codes With a Lookup Table

Magic numbers in code are the bare values like 3 or 9 that everyone remembers and nobody wrote down. The fix is not to renumber your data. Keep the codes exactly as they are, and give each one an approved name in a lookup table that the database enforces.

What does the 3 mean

A new developer reads a query that says WHERE StatusCode = 3 and asks, “Is that approved or shipped?” The person who knew left two years ago. The answer lives in an old email, or in somebody’s head, or in a comment that says “see spreadsheet.”

The demo starts with an orders table that has a StatusCode column and no lookup, just like thousands of real databases. It uses two plainly named tables and removes them at the end. Order 12 has code 5, which will matter later.

DROP TABLE IF EXISTS dbo.OrderDemo, dbo.OrderStatus;

CREATE TABLE dbo.OrderDemo (OrderId int PRIMARY KEY, StatusCode int NOT NULL);
INSERT dbo.OrderDemo VALUES (10, 3), (11, 1), (12, 5), (13, 9);

SELECT OrderId FROM dbo.OrderDemo WHERE StatusCode = 3;

The query returns order 10. It works, and it explains nothing. Before you label any code, confirm what it means. A frequent code is not always the active one, and a rare code is not always obsolete. Ask the owner, then watch how the application uses it.

Build the lookup table

Now create the lookup. The demo uses codes 1, 3 and 9, with the made-up names Open, Approved and Closed. They describe only this demo, not any real application. Keep the code and the name apart. The code is stable. A display caption may change later, for example when someone translates it.

Before adding any constraint to existing data, look for orders whose code has no matching row.

CREATE TABLE dbo.OrderStatus (
    StatusCode int PRIMARY KEY,
    StatusName nvarchar(40) NOT NULL UNIQUE
);
INSERT dbo.OrderStatus VALUES (1, N'Open'), (3, N'Approved'), (9, N'Closed');

SELECT o.OrderId, o.StatusCode
FROM dbo.OrderDemo AS o
LEFT JOIN dbo.OrderStatus AS s ON s.StatusCode = o.StatusCode
WHERE s.StatusCode IS NULL
ORDER BY o.OrderId;

The result is order 12 with code 5. Nobody gave 5 a name. This is the moment where renaming a literal in code would have hidden a real question.

Add the foreign key, and let it say no

Try to add the foreign key while that orphan is still there. SQL Server refuses with error 547. Then pretend the owner confirms that 5 means On hold. Add that row and run the same ALTER again.

BEGIN TRY
    ALTER TABLE dbo.OrderDemo WITH CHECK
        ADD CONSTRAINT FK_OrderDemo_OrderStatus FOREIGN KEY (StatusCode) REFERENCES dbo.OrderStatus (StatusCode);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number;
END CATCH;

INSERT dbo.OrderStatus VALUES (5, N'On hold');

ALTER TABLE dbo.OrderDemo WITH CHECK
    ADD CONSTRAINT FK_OrderDemo_OrderStatus FOREIGN KEY (StatusCode) REFERENCES dbo.OrderStatus (StatusCode);

The first attempt prints 547. The second succeeds quietly. WITH CHECK matters here. It makes SQL Server verify the old rows too. The last block shows the result: the constraint is marked as trusted.

From now on a bad code cannot get in. Try an order with code 7.

BEGIN TRY
    INSERT dbo.OrderDemo VALUES (14, 7);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number;
END CATCH;

SELECT o.OrderId, s.StatusCode, s.StatusName
FROM dbo.OrderDemo AS o
JOIN dbo.OrderStatus AS s ON s.StatusCode = o.StatusCode
ORDER BY o.OrderId;

Error 547 again, and the table keeps four orders. The join shows each order with its readable name, and the stored codes did not change. Nothing in the application had to move.

Move the callers gradually

Now rewrite the original query so a human can read it. It asks for the name, not the number.

SELECT o.OrderId
FROM dbo.OrderDemo AS o
JOIN dbo.OrderStatus AS s ON s.StatusCode = o.StatusCode
WHERE s.StatusName = N'Approved';

It returns order 10, the same as the magic number did. Go caller by caller and test each one. A code must never mean something new just because its label got easier to read.

Searching stored procedure text for literals will find many of them, but not all. It misses reordered comparisons, parameters and dynamic SQL. Read the call paths before you declare the job finished.

One index you may want

The lookup’s primary key supports the relationship. It does not index the StatusCode column in the orders table. This block first confirms that the foreign key is trusted, then lists the indexes on the orders table, then cleans up.

SELECT name AS constraint_name, is_not_trusted FROM sys.foreign_keys WHERE name = N'FK_OrderDemo_OrderStatus';

SELECT i.name AS index_name, c.name AS column_name
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID(N'dbo.OrderDemo');

DROP TABLE IF EXISTS dbo.OrderDemo, dbo.OrderStatus;

The constraint shows is_not_trusted as 0, which is what you want. Only the primary key on OrderId shows up in the index list. If you filter orders by status often, an index on StatusCode may help. Weigh it against the cost of maintaining it.

Pick one mystery number in your own database this week, and write down what it means.

A status code is not a business explanation, it is an identifier with an approved meaning.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.