Create database level constants (enumerations) without using CLR?
You can create an enumeration type in SQL Server using a XML Schema.

For example Colors.

create xml schema collection ColorsEnum as '
&lt;xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;xs:element name="Color"&gt;
        &lt;xs:simpleType&gt;
            &lt;xs:restriction base="xs:string"&gt; 
                &lt;xs:enumeration value="Red"/&gt;
                &lt;xs:enumeration value="Green"/&gt;
                &lt;xs:enumeration value="Blue"/&gt;
                &lt;xs:enumeration value="Yellow"/&gt;
            &lt;/xs:restriction&gt; 
        &lt;/xs:simpleType&gt;
    &lt;/xs:element&gt;
&lt;/xs:schema&gt;';


That allows you to use a variable or parameter of the type xml(dbo.ColorsEnum).

declare @Colors xml(dbo.ColorsEnum);
set @Colors = '&lt;Color&gt;Red&lt;/Color&gt;&lt;Color&gt;Green&lt;/Color&gt;'


If you try to add something that is not a color 

set @Colors = '&lt;Color&gt;Red&lt;/Color&gt;&lt;Color&gt;Ferrari&lt;/Color&gt;';


you get an error.

Msg 6926, Level 16, State 1, Line 43
XML Validation: Invalid simple type value: 'Ferrari'. Location: /*:Color[2]


Constructing the XML like that can be a bit tedious so you can for example create a helper view that also holds the allowed values.

create view dbo.ColorsConst as
select cast('&lt;Color&gt;Red&lt;/Color&gt;' as varchar(100)) as Red,
       cast('&lt;Color&gt;Green&lt;/Color&gt;' as varchar(100)) as Green,
       cast('&lt;Color&gt;Blue&lt;/Color&gt;' as varchar(100)) as Blue,
       cast('&lt;Color&gt;Yellow&lt;/Color&gt;' as varchar(100)) as Yellow;


And use it like this to create the enumration.

set @Colors = (select Red+Blue+Green from dbo.ColorsConst);


If you would like to create the view dynamically from the XML Schema you can extract the colors with this query.

select C.Name
from (select xml_schema_namespace('dbo','ColorsEnum')) as T(X)
  cross apply T.X.nodes('//*:enumeration') as E(X)
  cross apply (select E.X.value('@value', 'varchar(100)')) as C(Name);


The enumeration can of course also be used as parameters to functions and procedures.

create function dbo.ColorsToString(@Colors xml(ColorsEnum))
returns varchar(100)
as
begin
  declare @T table(Color varchar(100));

  insert into @T(Color)
  select C.X.value('.', 'varchar(100)')
  from @Colors.nodes('Color') as C(X);

  return stuff((select ','+T.Color
                from @T as T
                for xml path('')), 1, 1, '');
end


create procedure dbo.GetColors
  @Colors xml(ColorsEnum)
as
select C.X.value('.', 'varchar(100)') as Color
from @Colors.nodes('Color') as C(X);


declare @Colors xml(ColorsEnum) = '
&lt;Color&gt;Red&lt;/Color&gt;
&lt;Color&gt;Blue&lt;/Color&gt;
';

select dbo.ColorsToString(@Colors);

set @Colors = (select Red+Blue+Green from dbo.ColorsConst);
exec dbo.GetColors @Colors;