If an ID or reference is used more than once in a process, it’s best to store it in a variable and reference that. This can slow down debugging, but makes a process far more portable. It also reduces the number of magic numbers in a process (or at least times they are defined).

I started writing another post on magic numbers and this bit jumped out to be useful, but not what I wanted that post to focus on.

The problem

This is often seen in the wild in processes which produce an output/modify values for an individual customer. This customer is referenced by an ID (or name) in the code. In the future we want to repeat this process (or a variation on it) for another customer.

This becomes especially useful in processes where the ID is referenced many times (human error/forgetting to change all the references).

SELECT ...
  FROM customers c
 WHERE c.customer_id = 1
;

UPDATE c
   SET c.text_attribute = 'new value'
  FROM customers c
 WHERE c.customer_id = 1
;

In the above example, lines 3 and 9 both have a hard coded value, this can be replaced with a variable (which in the future could be given as an argument to a procedure).

The solution

Best practices suggest any ID/reference used should be declared as a variable at the start of a script/process. I do appreciate that this can increase debugging time (if a developer has to declare a variable for each statement they test as they test it).

This is a very quick and easy change to make to SQL code:

DECLARE @CustomerID INT = 1;

SELECT ...
  FROM customers c
 WHERE c.customer_id = @CustomerID
;

UPDATE c
   SET c.text_attribute = 'new value'
  FROM customers c
 WHERE c.customer_id = @CustomerID
;

The variable is defined on line 1, then referenced on 5 and 11.

Examples

Here are a couple of working examples, based on the code for my other “Magic Numbers” post.

• Problem SQL Fiddle
• Solution rextester (because SQL Fiddle does funny things with variables)