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).<\/p>\n
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.<\/p>\n
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.<\/p>\n
This becomes especially useful in processes where the ID is referenced many times (human error\/forgetting to change all the references).<\/p>\n
SELECT ...\r\n FROM customers c\r\n WHERE c.customer_id = 1\r\n;\r\n\r\nUPDATE c\r\n SET c.text_attribute = 'new value'\r\n FROM customers c\r\n WHERE c.customer_id\u00a0=\u00a01\r\n;<\/pre>\nIn 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).<\/p>\n
The solution<\/h1>\n
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).<\/p>\n
This is a very quick and easy change to make to SQL code:<\/p>\n
DECLARE @CustomerID INT = 1;\r\n\r\nSELECT ...\r\n FROM customers c\r\n WHERE c.customer_id = @CustomerID\r\n;\r\n\r\nUPDATE c\r\n SET c.text_attribute = 'new value'\r\n FROM customers c\r\n WHERE c.customer_id\u00a0=\u00a0@CustomerID\r\n;<\/pre>\nThe variable is defined on line 1, then referenced on 5 and 11.<\/p>\n
Examples<\/h2>\n
Here are a couple of working examples, based on the code for my other “Magic Numbers” post.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n