blocks|key|291126|text|在纯TSQL中，我能想到的唯一类似的事情就是返回所需vale的标量UDF。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|291127|在LINQ+to+SQL中，您可以将成员映射为c#/.NET枚举，它会为您处理它。|291128|但老实说，在大多数纯TSQL中，我主要使用文字。|291129|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

In pure TSQL the only similar thing I can think is a scalar UDF that returns the vale wanted.

In LINQ to SQL you can map members as c#/.NET enums, and it handles it for you.

But to be honest; in most pure TSQL I'd mainly just use the literals.

blocks|key|288725|text|您始终可以将枚举中的值传递到您尝试执行的已存储proc/命令中。这样，您就不必担心数据库中的枚举了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|288726|如果您希望将枚举存储在数据库中，那么我建议您创建一个视图(可能以您的枚举为标题)，如下所示：|288727|create+view+JobStatus
++++select+0+as+Ready,+1+as+Running,+2+as+Cancelling|code-block|syntax|javascript|288728|然后，如果需要，您可以访问/加入视图。|288729|请注意，查询优化器将上述引用视为常量扫描/标量操作，而不是表扫描，因此不会引起访问实际表时发生的读取。|288730|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can always pass the value from the enumeration into the stored proc/command that you are trying to execute. This way, you never have to worry about the enumerations in the database.

If you want to store the enumerations in the database, then I suggest you create a view (possibly titled with your enumeration), like so:

<pre><code>create view JobStatus
 select 0 as Ready, 1 as Running, 2 as Cancelling
</code></pre>

You can then access/join on the view if you need it to.

Note, the query optimizer treats any reference to the above as a constant scan/scalar operation, not a table scan, so you aren't incurring the reads that would occur if you were accessing an actual table.

blocks|key|3218377|text|在一个项目中，我们定义了一个属性，该属性应用于存储数据库中预期的表和值的每个枚举成员，并且单元测试验证了该链接。不过还是很乱。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3218378|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In one project we defined an attribute that applied to every enum member that stored the table and value expected in the database and the unit tests verified the link. Messy though.

blocks|key|287896|text|也许你可以在SQL服务器上实现托管代码，而不是使用普通的TSQL？不能百分之百确定这是否可行，但这可能是一个值得探索的选择。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|287897|Possible+Solution+here...|offset|length|287898|entityMap|0|LINK|mutability|MUTABLE|url|http://forum.strataframe.net/Topic10664-14-1.aspx^0|0|0|P|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

Perhaps you could implement managed code on the SQL server instead of using normal TSQL? Not 100% sure if this would work, but it might be an option to explore.

<a href="http://forum.strataframe.net/Topic10664-14-1.aspx" rel="nofollow">Possible Solution here...</a>

blocks|key|288749|text|为此，我喜欢使用表值函数。它们可以很好地一起封装和打包变量，几乎就像其他语言中的命名枚举一样。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|288750|假设我们有几个变量对应于不同的订单状态：|288751|288752|新建=+100|unordered-list-item|288753|In+progress+=+250|288754|Completed+=+500|288755|288756|我要做的是创建一个返回变量的函数|288757|CREATE+FUNCTION+dbo.OrderStatus()
RETURNS+@T_def+TABLE+
(
++++NEW+int,
++++IN_PROGRESS+int,
++++COMPLETED+int
)
AS
BEGIN
++++INSERT+INTO+@T_def+(NEW,+IN_PROGRESS,+COMPLETED)
++++++++VALUES+(100,+250,+500);
++++RETURN+
END|code-block|syntax|javascript|288758|我现在可以在查询中使用我的变量，甚至智能也会提示我有关变量的信息：|288759|SELECT
++++OrderStatus.NEW
FROM+dbo.OrderStatus()+"OrderStatus";|288760|或作为使用CROSS+APPLY的另一个查询的一部分|288761|SELECT
++++OrderStatus.NEW
+++,*
FROM+CUSTOMER_ORDERS
CROSS+APPLY+dbo.OrderStatus()+"OrderStatus";|288762|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|15|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|16|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|17|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|18|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1A|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|1B|8|@]|9|@]|A|$R|S]]|$1|T|3|U|5|6|7|1C|8|@]|9|@]|A|$]]|$1|V|3|W|5|Q|7|1D|8|@]|9|@]|A|$R|S]]|$1|X|3|Y|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Z|3|10|5|Q|7|1F|8|@]|9|@]|A|$R|S]]|$1|11|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|12|$]]

I like to use table valued functions for this. They work well with encapsulating and packaging variables together, almost like named enums from other languages.
Let's say we a couple of variables for different order statuses:
<ul>
<li>New = 100</li>
<li>In progress = 250</li>
<li>Completed = 500</li>
</ul>
What I would do is create a function that returns my variables
<pre><code>CREATE FUNCTION dbo.OrderStatus()
RETURNS @T_def TABLE 
(
 NEW int,
 IN_PROGRESS int,
 COMPLETED int
)
AS
BEGIN
 INSERT INTO @T_def (NEW, IN_PROGRESS, COMPLETED)
 VALUES (100, 250, 500);
 RETURN 
END
</code></pre>
I can now use my variables in my queries, and even intellisence will work to hint me about the variables:
<pre><code>SELECT
 OrderStatus.NEW
FROM dbo.OrderStatus() &quot;OrderStatus&quot;;
</code></pre>
Or as part of another query using CROSS APPLY
<pre><code>SELECT
 OrderStatus.NEW
 ,*
FROM CUSTOMER_ORDERS
CROSS APPLY dbo.OrderStatus() &quot;OrderStatus&quot;;
</code></pre>

We have enums in our C# code:

<pre><code>public enum JobStatus
{ 
 Ready = 0, 
 Running = 1, 
 Cancelling = 2,
}
</code></pre>

These values are also stored in database fields, and we have lots of TSQL (mostly stored procs, as well as some batches and SSIS) that also processes the data: 

<pre><code>SELECT TOP 1 @JobSID = JobSID
FROM Job 
WHERE Status = 0 /* JobStatus.Ready */
ORDER BY SubmitDate ASC

CREATE TABLE ImportCrossEffect(
 /* lots deleted */ 
 Source tinyint 
 DEFAULT 1 NOT NULL -- 0: Unknown (default), 1:Imported, 2:Keyed
) 
</code></pre>

How do I avoid hard coding the “magic numbers” in the TSQL? 
How do I remove the risk of the enumerations not matching on the C# and TSQL sides?

(I have included the C# tag, as I would like solution that “single sourced” the definitions of the enums on both the C# and TSQL sides) 

<hr>

Updates:

We don't have tables in the database with the Enum names in them, the values are just stored in tiny int columns.

I was hoping for something like a SQL pre-processor that would "expand" all the enum to there "magic value".

How do I use enums in TSQL without hard coding magic number all over my SQL scripts/procs?

单元测试

数据库

服务器

我们的C#代码中有枚举：public enum JobStatus{          Ready = 0,        Running = 1,          Cancelling = 2,}这些值也存储在数据库字段中，我们有TSQL的lots (主要是存储的procs，以及一些批处理和SSIS)，它也处理数据...

问我如何在TSQL中使用枚举，而不是在我的SQL脚本/过程中使用硬编码魔术数字？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我如何在TSQL中使用枚举，而不是在我的SQL脚本/过程中使用硬编码魔术数字？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我如何在TSQL中使用枚举，而不是在我的SQL脚本/过程中使用硬编码魔术数字？
EN