Create database level constants (enumerations) without using CLR?

I have a several SQL objects that need to take alternate actions based on a desired state of the request. Is there a way to create database level constants (enumerations) that can be passed to stored procedures, table-valued functions, and used in queries (without using CLR)?

CREATE PROCEDURE dbo.DoSomeWork(@param1 INTEGER, ..., @EnumValue myEnumType)  AS ...;

and then use it:

EXEC doSomeWork 85, ..., (myEnumType.EnumValue1 + myEnumType.EnumValue2);

Where myEnumType would hold a few enumeration values.

In the procedure I would be able to use @EnumValue and test it against values in myEnumType to do the required work. I would make the values of myEnumType a bitmask for the case I am considering.

For a simple example, consider an expensive process that takes a huge dataset and reduces it to a smaller but still very large dataset. In this process you need to make some adjustment in the middle of that process that will affect the result. Say this is filter for (or against) some types of records based on some status of an intermediate calculation within the reduction. The @EnumValue of type myEnumType could be used to test for this

SELECT   ...
FROM     ...
WHERE       (@EnumValue & myEnumType.EnumValue1 = myEnumType.EnumValue1 AND ...)
        OR  (@EnumValue & myEnumType.EnumValue2 = myEnumType.EnumValue2 AND ...)
        OR  ...

Are these sort of database level constants possible in SQL Server without the use of CLR?

I am seeking a database level enumeration or set of constants that can be passed as parameters to stored procedures, functions, and so on.

You can create an enumeration type in SQL Server using a XML Schema.

For example Colors.

create xml schema collection ColorsEnum as '

    
        
             
                
                
                
                
             
        
    
';

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

declare @Colors xml(dbo.ColorsEnum);
set @Colors = 'RedGreen'

If you try to add something that is not a color

set @Colors = 'RedFerrari';

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('Red' as varchar(100)) as Red,
       cast('Green' as varchar(100)) as Green,
       cast('Blue' as varchar(100)) as Blue,
       cast('Yellow' 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) = '
Red
Blue
';

select dbo.ColorsToString(@Colors);

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


StackExchange Database Administrators Q#171194, answer score: 10