摆脱SQL Server中的“魔术”数字

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'.

在代码中,可以指定全局可访问的常量/枚举/等,然后可以在整个应用程序中重用它们。这使得能够使用像'Mazda'这样有意义的名称而不是像'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.

我们希望在我们的SQL Server存储过程中执行相同操作,但不确定实现此操作的最佳方法。

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:

我们的一个限制是我们不能依赖于制造商。名称在应用程序的生命周期中保持不变。我们确实考虑过一个永远不会改变的“Code”列,并且查找联接:

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?

无需在每个存储过程中声明变量,最好的方法是什么?

2 个解决方案

#1

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.

使用nchar(4)列而不是int列,并提出稍微不易察觉的代码。所以日产将是“NISN”而不是1.更好一点。

#2

You don't need to store numbers in the database. There are DB admins out there who still love the idea of 'natural keys'. Instead of using an ID, they simply use a natural key that uniquely defines the manufacturer. Often this can lead to multiple primary keys. You might have to have a 'Mazda' 'Canada' as a unique identifier for manufacturer.

您不需要在数据库中存储数字。有DB管理员仍然喜欢'自然键'的想法。他们只使用唯一定义制造商的自然键,而不是使用ID。通常这会导致多个主键。您可能必须将“Mazda”“Canada”作为制造商的唯一标识符。

Personally, I dislike this method, and prefer to have a unique ID for every identifiable object stored in my tables. This means creating a new table which is a lookup. 2, 'Mazada'. Then you can create a view that pulls 'Mazda' in where there is 2 in the database, then run the query against the view. SELECT * from v_cars where maker = 'Mazda'

就个人而言,我不喜欢这种方法,并且更喜欢为我的表中存储的每个可识别对象都有一个唯一的ID。这意味着创建一个新表,这是一个查找。 2,'马扎达'。然后,您可以创建一个视图,将“Mazda”拉入数据库中的2,然后针对视图运行查询。 SELECT * from v_cars其中maker ='Mazda'

#1