Getting rid of "Magic" numbers in SQL Server
sql, sql-server
Solution
We've done this a couple ways:
- User-defined function that returns the "magic number" by name.
- Use a nchar(4) column instead of int column and come up with slightly less inscrutable codes. So Nissan would be "NISN" instead of 1. A little nicer.
Problem
In code one can specify globally accessible constants/enums/etc once that can then be reused through out the application. This gives the ability to use meaningful names like 'Mazda' instead of numbers like '2'. We would like to do the same with in our SQL Server stored procedures, but are not sure of the best way of implementing this. E.g for the following tables (The proverbial car schema): ``` Car ManufacturerId 350Z 1 Hilux 2 Yaris 2 ManufacturerId Name 1 Nissan 2 Toyota ``` So instead of writing ``` SELECT * FROM Car WHERE ManufacturerId = 1 -- Nissan ``` We would like to write something like ``` SELECT * FROM Car WHERE ManufacturerId = @Nissan ``` One restriction we have is that we cannot rely on the Manufacturer.Name staying the same for the life of the App. We did think about having a column "Code" that never changes and the joins are looked up like this: ``` SELECT * FROM Car c INNER JOIN Manufacturer m ON c.ManufacturerId = m.ManufacturerId WHERE m.Code = 'Nissan' ``` I'm a bit hesitant on this as it uses an extra join and string comparisons that can be misspelled. What is the best way for this to be accomplished without having to declare the variables in every Stored Procedure?