No Magic Numbers
Today I wanted to share a tip for SQL queries to make them more manageable. Recently I have been translating some SQL code into C#. One of the things that made the process more difficult is that the SQL code was written using a variable @X INT. The code takes different branches depending on the value of @X.
IF @X = 1 Do something
ELSE IF @X = 2 Do Something Different
This isn’t bad code, and works fine. The problem I have run into is that I don’t know the difference between “Do Something” and “Do Something Different”. Since I didn’t write the code I have to then go and review the different branches to determine what makes them different.
There are a few options here for SQL code. Sometimes it’s possible to make different stored procedures called based on the value of @X. With separate procedures, the name of the procedure provides information about what is going on.
You could use a different name for the variable @X. That doesn’t tell me much…but a variable named @Status gives me a much better clue about what is going on.
Another option would be to place comments in the code. I don’t want to start a religious war about comments. I’d rather have them if the code is not clear…but you can write the code so that it is self documenting.
One technique from procedural languages is the use of an ENUM. Using an ENUM the value 1 actually has a name, as does every other option. SQL Server doesn’t support ENUMs. Instead I use local variables providing a self documenting comment as to what the value represents.
Let’s demonstrate this technique for a Status variable that has three values:
DECLARE
@StatusActive INT = 1
,@StatusInactive INT = 2
,@StatusDeleted INT = 3
Now instead of having a statement
IF @X = 1
I can replace it with
IF @X = @StatusActive
Hey, no more magic numbers. I know what 1 means. I know why I am branching. I have a clue about what is being done when a new branch is called in the TSQL code. And I didn’t have to write a comment every time I test the value of @X.
This isn’t as nice as an ENUM in a procedural language…but it is a lot better than Magic Numbers.
Do you have any other tips you’d like to share…Drop me a note at facebook, twitter, or email at btaylor@SSWUG.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)
Featured Script
Script _Out_Data
VBScript that generates INSERT Statements based on existing table contents for the purpose of migrating data when BCP or DTS … (read more)
Browser Update
I received a reply from Chris Sutton, one of the presenters for the DBTechCon coming up next month. You’ll definitely want to check out his session if you are using Dot Net for your Web Server.
Chris was responding to the editorial from yesterday regarding tools to help reduce the differences we have to code for different versions/brands of browsers.
Chris Writes:
Regarding your sswug email today…
Two libraries beyond jQuery, that you might want to check out are http://www.modernizr.com/ and http://html5boilerplate.com/
I’ve just been researching them at this point, but will probably incorporate the boilerplate library in a project soon since it does a lot of grunt work to help normalize across browsers.
While your at it go check out DBTechCon.
Ben