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

Question

我有几个SQL对象，需要根据请求的期望状态采取不同的操作。是否有一种方法可以创建数据库级别的常量（枚举），可以传递给存储过程、表值函数，并在查询中使用（而不使用CLR）？

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

然后使用它：

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

在这个过程中，我可以使用@EnumValue并将其与myEnumType中的值进行比较，以完成所需的工作。我会将myEnumType的值设置为位掩码，以便考虑到我正在考虑的情况。

举个简单的例子，考虑一个耗时的过程，它将一个庞大的数据集缩小为一个仍然非常庞大的数据集。在这个过程中，您需要在该过程的中间进行一些调整，这些调整将影响结果。假设这是基于减少过程中某个中间计算的某种状态对某些类型的记录进行过滤（或反对）的筛选器。类型为myEnumType的@EnumValue可以用来测试这个。

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

这种类型的数据库级常量在SQL Server中是否可以实现，而不需要使用CLR？ 我正在寻找一种数据库级枚举或常量集，可以作为参数传递给存储过程、函数等。

- Edmund

Answer 1

你可以在SQL Server中使用XML Schema创建一个枚举类型。 例如，颜色。

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 Schema动态创建视图，您可以使用以下查询提取颜色。

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;

- Mikael Eriksson

Answer 2

既然您显然正在使用SQL Server 2016，我想提出另一个“可能”的选择 - SESSION_CONTEXT。

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

总结一些关键点：

如果您曾经想要在数据库连接的整个生命周期中共享会话状态，无论是存储过程还是批处理，您一定会喜欢 SESSION_CONTEXT。当连接到 SQL Server 2016 时，您将获得一个有状态的字典，通常被称为状态包，可以在其中存储字符串、数字等值，并通过所分配的键检索它。在 SESSION_CONTEXT 的情况下，键可以是任意字符串，值是 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

我甚至尝试更改“read_only”上下文信息。

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

并收到了一个错误：

Msg 15664，级别 16，状态 1，过程 sp_set_session_context，行 1 [批处理开始行 8] 无法在会话上设置键“CannotChange” 上下文。该键已被设置为此会话的只读。

关于登录触发器的重要说明（来自此帖子）！

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

---

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

1. 给你的Session_Context名称-值对加上数据库名称的前缀，以避免在另一个数据库中引起相同类型名称的冲突。这并不能解决为所有用户预定义所有Session_Context名称-值的问题。
2. 当登录触发器触发时，您可以访问EventData（xml），您可以使用它来提取出登录用户，并基于此创建特定的Session_Context名称-值对。

- Scott Hodgin - Retired

Answer 3

在SQL Server中是不允许的（虽然我记得在1998年的时候可以在Oracle程序包中创建常量，而且一直以来在SQL Server中都觉得缺少这个功能）。 并且，我刚刚测试了一下，发现即使在SQLCLR中也无法做到这一点，至少不能在所有情况下都有效。阻碍的原因是存储过程参数的限制。似乎在参数名称中不能使用“.”或者“::”。我尝试了一下：

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

-- and:

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

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

Msg 102, Level 15, State 1, Line xxxxx
附近有错误的语法。

另一方面，这两种方法对于用户定义的函数参数确实有效。

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

-- and:

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

所以，你真正能做的最好的事情就是使用SQLCLR来直接处理UDF、TVF、UDAs（我猜测），以及查询，并在需要与存储过程一起使用时将其赋值给本地变量。

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

这是我在有机会使用实际枚举值时采取的方法（与应该在特定用途/含义的查找表中的查找值相对）。

---

关于尝试使用用户定义函数（UDF）来输出“常量”/“枚举”值，我也无法使其在将其作为存储过程参数传递方面工作。

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

返回“语法错误”错误，SSMS会将括号内的所有内容都标记出来，即使我用数字替换字符串，或者如果没有要传递的参数，则会标记右括号。

- Solomon Rutzky