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?