What are the different ways of handling 'Enumerations' in SQL server?

Question

We currently define a list of constants (mostly these correspond to enumerations we have defined in the business layer) at the top of a stored procedure like so:

DECLARE @COLOR_RED INT = 1
DECLARE @COLOR_GREEN INT = 2
DECLARE @COLOR_BLUE INT = 3

But these often get repeated for many stored procedures so there is a lot of duplication.

Another technique I use if the procedure needs just one or two constants is to pass them in as parameters to the stored procedure. (using the same convention of upper case for constant values). This way I'm sure the values in the business layer and data layer are consistent. This method is not nice for lots of values.

What are my other options?

I'm using SQL Server 2008, and C# if it makes any difference.

Update Because I'm using .Net is there any way that user defined (CLR) types can help?

Answer 1

This might be controversial: my take is don't use enumerations in T-SQL. T-SQL isn't really designed in a way that makes enums useful, the way they are in other languages. To me, in T_SQL, they just add effort and complexity without the benefit seen elsewhere.