tags:

views:

172

answers:

2

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?

+2  A: 

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.
Aidan Ryan
Toyed with the first suggestion, but it's way too late for the second one. Maybe next time.
Robert Wagner
The thing that gets difficult with this issue is that the magic number usually corresponds to an enum in your client code. At some point you will screw up syncing your SQL lookup with the client enum. MS SQL Server offers a very tidy integration point here: you can code a CLR UDF to return the enum value, essentially enforcing some runtime type safety. You still need to make sure your queries get the names of the enum values right though, I've never found a way around this part of the problem.
Aidan Ryan
+1  A: 

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.

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'

Kieveli
Besides being to late to change the arrangement I agree with you on not using natural keys in these cases as it makes it easier to use enums in code.I like the view idea.
Robert Wagner