StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL: avoiding hard-coding or magic numbers
text
Body
copied!<p><strong>Question:</strong> What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures?</p> <p>Consider a stored procedure whose job is to check/update a value of a record based on its <code>StatusID</code> or some other FK lookup table or range of values.</p> <p>Consider a <code>Status</code> table where the ID is most important, as it's a FK to another table:</p> <p><img src="https://imgur.com/HEb6i.png" alt="alt text"></p> <p>The SQL scripts that are to be avoided are <em>something like</em>:</p> <pre><code>DECLARE @ACKNOWLEDGED tinyint SELECT @ACKNOWLEDGED = 3 --hardcoded BAD UPDATE SomeTable SET CurrentStatusID = @ACKNOWLEDGED WHERE ID = @SomeID </code></pre> <p>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.</p> <p>Also trying to avoid a <code>SELECT</code> based on the text description/name of the status:</p> <pre><code>UPDATE SomeTable SET CurrentStatusID = (SELECT ID FROM [Status] WHERE [Name] = 'Acknowledged') WHERE ID = @SomeID </code></pre> <p><strong>Question:</strong> What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures?</p> <p>Some other thoughts on how to achieve this:</p> <ul> <li>add a new <code>bit</code> column (named like 'IsAcknowledged') and sets of rules where there can be only one row with a value of <code>1</code>. This would help in finding the unique row: <code>SELECT ID FROM [Status] WHERE [IsAcknowledged] = 1)</code></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload