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 '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Color">
        <xs:simpleType>
            <xs:restriction base="xs:string"> 
                <xs:enumeration value="Red"/>
                <xs:enumeration value="Green"/>
                <xs:enumeration value="Blue"/>
                <xs:enumeration value="Yellow"/>
            </xs:restriction> 
        </xs:simpleType>
    </xs:element>
</xs:schema>';

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

declare @Colors xml(dbo.ColorsEnum);
set @Colors = '<Color>Red</Color><Color>Green</Color>'

If you try to add something that is not a color

set @Colors = '<Color>Red</Color><Color>Ferrari</Color>';

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

select dbo.ColorsToString(@Colors);

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

Since you are apparently using SQL Server 2016, I'd like to throw out another 'possible' option - SESSION_CONTEXT.

Leonard Lobel's article, Sharing State in SQL Server 2016 with SESSION_CONTEXT has some very good information about this new functionality in SQL Server 2016.

Summarizing some key points:

If you’ve ever wanted to share session state across all stored procedures and batches throughout the lifetime of a database connection, you’re going to love SESSION_CONTEXT. When you connect to SQL Server 2016, you get a stateful dictionary, or what’s often referred to as a state bag, some place where you can store values, like strings and numbers, and then retrieve it by a key that you assign. In the case of SESSION_CONTEXT, the key is any string, and the value is a sql_variant, meaning it can accommodate a variety of types.

Once you store something in SESSION_CONTEXT, it stays there until the connection closes. It is not stored in any table in the database, it just lives in memory as long as the connection remains alive. And any and all T-SQL code that’s running inside stored procedures, triggers, functions, or whatever, can share whatever you shove into SESSION_CONTEXT.

The closest thing like this we’ve had until now has been CONTEXT_INFO, which allows you to store and share a single binary value up to 128 bytes long, which is far less flexible than the dictionary you get with SESSION_CONTEXT, which supports multiple values of different data types.

SESSION_CONTEXT is easy to use, just call sp_set_session_context to store the value by a desired key. When you do that, you supply the key and value of course, but you can also set the read_only parameter to true. This is locks the value in session context, so that it can’t be changed for the rest of the lifetime of the connection. So, for example, it’s easy for a client application to call this stored procedure to set some session context values right after it establishes the database connection. If the application sets the read_only parameter when it does this, then the stored procedures and other T-SQL code that then executes on the server can only read the value, they can’t change what was set by the application running on the client.

As a test, I created a server login trigger which sets some CONTEXT_SESSION information - one of the SESSION_CONTEXT's was set to @read_only.

DROP TRIGGER IF EXISTS [InitializeSessionContext] ON ALL SERVER
GO
CREATE TRIGGER InitializeSessionContext ON ALL SERVER
FOR LOGON AS

BEGIN

    --Initialize context information that can be altered in the session
    EXEC sp_set_session_context @key = N'UsRegion'
        ,@value = N'Southeast'

    --Initialize context information that cannot be altered in the session
    EXEC sp_set_session_context @key = N'CannotChange'
        ,@value = N'CannotChangeThisValue'
        ,@read_only = 1

END;

I logged in as a completely new user and was able to extract the SESSION_CONTEXT information:

DECLARE @UsRegion varchar(20)
SET @UsRegion = CONVERT(varchar(20), SESSION_CONTEXT(N'UsRegion'))
SELECT DoThat = @UsRegion

DECLARE @CannotChange varchar(20)
SET @CannotChange = CONVERT(varchar(20), SESSION_CONTEXT(N'CannotChange'))
SELECT DoThat = @CannotChange

I even attempted to change the 'read_only' context information:

EXEC sp_set_session_context @key = N'CannotChange'
    ,@value = N'CannotChangeThisValue'

and received an error:

Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 1 [Batch Start Line 8] Cannot set key 'CannotChange' in the session context. The key has been set as read_only for this session.

An important note about login triggers (from this post)!

A logon trigger can effectively prevent successful connections to the Database Engine for all users, including members of the sysadmin fixed server role. When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Database Engine in minimal configuration mode (-f)

---

One potential drawback is that this fills session context instance wide (not per database). At this point, the only options I can think of are:

1. Name your Session_Context name-value pairs by prefixing them with the database name so as not to cause a collision for the same type name in another database. This doesn't solve the problem of pre-defining ALL Session_Context name-values for all users.
2. When the login trigger fires, you have access to EventData (xml) which you could use to extract out the login user and based on that, you could create specific Session_Context name-value pairs.

In SQL Server, no (though I recall creating constants in Oracle packages back in 1998 and have kinda missed having them in SQL Server).

AND, I just tested and found that you cannot even do this with SQLCLR, at least not in the sense that it would work in all cases. The hold up is the restrictions on Stored Procedure parameters. It seems that you cannot have either a . or :: in the parameter name. I tried:

EXEC MyStoredProc @ParamName = SchemaName.UdtName::[StaticField];

-- and:

DECLARE @Var = SchemaName.UdtName = 'something';
EXEC MyStoredProc @ParamName = @Var.[InstanceProperty];

In both cases it did not even get past the parsing phase (verified using SET PARSEONLY ON;) due to:

Msg 102, Level 15, State 1, Line xxxxx
Incorrect syntax near '.'.

On the other hand, both methods did work for User-Defined Function parameters:

SELECT MyUDF(SchemaName.UdtName::[StaticField]);

-- and:

DECLARE @Var = SchemaName.UdtName = N'something';
SELECT MyUDF(@Var.[InstanceProperty]);

So, the best you can really do is use SQLCLR to have something that works directly with UDFs, TVFs, UDAs (I assume), and queries, and then assign to local variables when needing to use with Stored Procedures:

DECLARE @VarInt = SchemaName.UdtName::[StaticField];
EXEC MyStoredProc @ParamName = @VarInt;

This is the approach I have taken when there is an opportunity to have an actual enum value (as opposed to a lookup value which should be in a lookup table specific to its usage / meaning).

---

With respect to attempting this with a User-Defined Function (UDF) to spit out the "constant" / "enum" value, I couldn't get that to work either in terms of passing it in as a Stored Procedure parameter:

EXEC MyStoredProc @ParamName = FunctionName(N'something');

returns the "Incorrect syntax" error, with SSMS highlighting everything within the parentheses, even if I replace the string with a number, or the right parenthesis if there is no parameter to pass in.