在不使用 CLR 的情况下创建数据库级常量（枚举）？

您可以使用 XML 架构在 SQL Server 中创建枚举类型。

例如颜色。

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>';

这允许您使用类型的变量或参数xml(dbo.ColorsEnum)。

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

如果您尝试添加不是颜色的东西

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

你得到一个错误。

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

像这样构建 XML 可能有点乏味，因此您可以例如创建一个也包含允许值的辅助视图。

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;

并像这样使用它来创建枚举。

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

如果您想从 XML 模式动态创建视图，您可以使用此查询提取颜色。

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);

枚举当然也可以用作函数和过程的参数。

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;

由于您显然使用的是 SQL Server 2016，我想抛出另一个“可能的”选项 - SESSION_CONTEXT。

Leonard Lobel 的文章，在 SQL Server 2016 中共享状态，提供SESSION_CONTEXT了一些关于 SQL Server 2016 中的新功能的非常好的信息。

总结几个关键点：

如果您曾经想在数据库连接的整个生命周期内跨所有存储过程和批处理共享会话状态，那么您一定会喜欢SESSION_CONTEXT. 当你连接到 SQL Server 2016 时，你会得到一个有状态的字典，或者通常被称为状态包的东西，你可以在某个地方存储值，如字符串和数字，然后通过你分配的键检索它。在 的情况下SESSION_CONTEXT，key 是任意字符串，value 是 sql_variant，意味着它可以容纳多种类型。

一旦您将某些内容存储在 中SESSION_CONTEXT，它就会一直存在，直到连接关闭。它不存储在数据库中的任何表中，只要连接保持活动状态，它就存在于内存中。在存储过程、触发器、函数或其他任何东西中运行的任何和所有 T-SQL 代码都可以共享您插入的任何内容 SESSION_CONTEXT。

到目前为止，我们所拥有的最接近的东西是CONTEXT_INFO，它允许您存储和共享长达 128 字节的单个二进制值，这远不如您使用的字典灵活SESSION_CONTEXT，后者支持不同数据的多个值类型。

SESSION_CONTEXT易于使用，只需调用 sp_set_session_context 以通过所需的键存储值。当您这样做时，您当然会提供键和值，但您也可以将 read_only 参数设置为 true。这是锁定会话上下文中的值，以便在连接的剩余生命周期内无法更改它。因此，例如，客户端应用程序很容易在建立数据库连接后立即调用此存储过程来设置一些会话上下文值。如果应用程序在执行此操作时设置了 read_only 参数，那么随后在服务器上执行的存储过程和其他 T-SQL 代码只能读取该值，它们无法更改运行在客户端上的应用程序设置的内容。

作为测试，我创建了一个服务器登录触发器，它设置了一些CONTEXT_SESSION信息——其中一个SESSION_CONTEXT设置为@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;

我以全新用户身份登录并能够提取SESSION_CONTEXT信息：

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

我什至尝试更改“只读”上下文信息：

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

并收到一个错误：

消息 15664，级别 16，状态 1，过程 sp_set_session_context，第 1 行 [批处理开始第 8 行] 无法在会话上下文中设置键“CannotChange”。此会话的密钥已设置为 read_only。

关于登录触发器的重要说明（来自这篇文章）！

登录触发器可以有效地阻止所有用户成功连接到数据库引擎，包括 sysadmin 固定服务器角色的成员。当登录触发器阻止连接时，sysadmin 固定服务器角色的成员可以通过使用专用管理员连接或以最小配置模式 (-f) 启动数据库引擎进行连接

一个潜在的缺点是这会填充会话上下文实例范围（而不是每个数据库）。在这一点上，我能想到的唯一选择是：

1. 通过在名称Session_Context-值对前面加上数据库名称来命名它们，以免在另一个数据库中导致相同类型名称的冲突。这并不能解决Session_Context为所有用户预定义所有名称值的问题。
2. 当登录触发器触发时，您可以访问EventData(xml)，您可以使用它来提取登录用户，并基于此，您可以创建特定Session_Context的名称-值对。

在 SQL Server 中，没有（尽管我记得 1998 年在 Oracle 包中创建常量并且有点怀念在 SQL Server 中使用它们）。

而且，我刚刚进行了测试，发现您甚至无法使用 SQLCLR 执行此操作，至少在它在所有情况下都可以使用的意义上。阻碍是对存储过程参数的限制。似乎您不能在参数名称中包含 a.或。::我试过了：

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

-- and:

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

在这两种情况下，它甚至都没有通过解析阶段（使用 验证SET PARSEONLY ON;），原因是：

消息 102，级别 15，状态 1，第 xxxxx 行
'.' 附近的语法不正确。

另一方面，这两种方法都适用于用户定义的函数参数：

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

-- and:

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

因此，您真正能做的最好的事情是使用 SQLCLR 让某些东西直接与 UDF、TVF、UDA（我假设）和查询一起使用，然后在需要与存储过程一起使用时分配给局部变量：

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

这是我在有机会获得实际枚举值时采用的方法（与查找值相反，查找值应该位于特定于其用法/含义的查找表中）。

关于尝试使用用户定义函数 (UDF) 来吐出“常量”/“枚举”值，我无法让它工作，要么将其作为存储过程参数传递：

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

返回“语法不正确”错误，SSMS 突出显示括号内的所有内容，即使我用数字替换字符串，或者如果没有要传入的参数则替换右括号。