Geek Answers Handbook

Getting Rid of Magic Numbers in SQL Server

The code can specify globally accessible constants / enumerations / etc, which can then be reused through the application. This makes it possible to use meaningful names such as "Mazda" instead of numbers like "2".

We would like to do the same in our SQL Server stored procedures, but are unsure of the best way to implement this.

For example, for the following tables (notorious car scheme):

Car       ManufacturerId 
350Z       1
Hilux      2
Yaris      2

ManufacturerId   Name
1                Nissan
2                Toyota

Therefore, instead of writing

SELECT * FROM Car WHERE ManufacturerId = 1 -- Nissan

We would like to write something like

SELECT * FROM Car WHERE ManufacturerId = @Nissan

The only limitation that we have is that we cannot rely on the name Manufacturer.Name, remaining unchanged throughout the life of the application. We really thought that the Code column never changes and the connections look like this:

SELECT * 
FROM Car c 
    INNER JOIN Manufacturer m ON c.ManufacturerId = m.ManufacturerId
WHERE m.Code = 'Nissan'

, , .

, ?

+5
2

:

  • , " " .
  • nchar (4) int . , Nissan "NISN" 1. .
+4

. , " ". , . . , "Mazda" "" .

, . , . 2, "". , "", 2 , . SELECT * v_cars, maker = 'Mazda'

+1

More articles:


All Articles