Isn't it called Dictionary Objects?

http://www.microsoft.com/technet/scriptcenter/scripts/misc/diction/default.mspx?mfr=true

Dictionary Objects

Although an admirable effort, the author misses the mark.

I would urge readers to, instead, push the folks at Microsoft to implement proper support for relational domains. This is the superior and theoretically sound solution.

TroyK

I don’t see any point to the idea of ENUMS; basically, it solves a problem that doesn’t exist.

There is nothing wrong with any of the techniques in the code below, other than the author not liking to write it.

I will say that you should never use a hard coded ID value; that defeats the whole purpose of a surrogate key by hard-coding the meaning of a particular ID.

--Method 1

select

      Order.OrderId,

      Order.OrderDate,

      Order.PaymentTypeId

from

      Order

      join

      PaymentType

      on Order.PaymentTypeId = PaymentType.PaymentTypeId

where

      PaymentTypeDescription = 'Invoice'

or

--Method 2

select

      Order.OrderId,

      Order.OrderDate,

      Order.PaymentTypeId

from

      Order

where

      Order.PaymentTypeId in (

      select

            PaymentType.PaymentTypeId

      from

            PaymentType

      where

            PaymentTypeDescription = 'Invoice'

      )

or

--Method 3

declare @PaymentTypeId int

select

      @PaymentTypeId = PaymentType.PaymentTypeId

from

      PaymentType

where

      PaymentTypeDescription = 'Invoice'

select

      Order.OrderId,

      Order.OrderDate,

      Order.PaymentTypeId

from

      Order

where

      Order.PaymentTypeId = @PaymentTypeId

In the SQL 2005 world, I've wondered (but not implemented) code using the SQLCMD metacommand :setvar and :r.  One could use these like #define and #include in C++.

File1.sql
#setvar enumMale M

#setvar enumFemale F

File2.sql:

:r file1.sql

select Name from Person where Sex = '$(enumMale)'

The above is contrived and simplistic, but you get the idea - rather than going for enum, just go for symbolic substitution.