等效于MS-SQL Server 2005 Express，2005 Full和更高版本的内联IIF [英] Inline IIF equivalent for MS-SQL Server 2005 Express, 2005 Full, and forward

问题描述

我正在尝试编写一些代码，这些代码将在一个SQL查询中获取我的所有信息，但是对它们进行排序以便有两个组，然后每个组都是字母顺序的.

我当前的查询(接近)是:

SELECT *，PATINDEX(''％heartbeat％''，lower(TimerName))如IsHeartBeat从tblTimer排序于IsHeartBeat，TimerName，TimerInterval，TimerPriority

PATINDEX正确地将我的所有心跳计时器置于结果集的末尾...但是由于它不仅仅返回0或1，所以返回true或false，它会根据在心跳中找到心跳一词的位置对心跳进行排序名称，而不是按字母顺序.

我想要一个内联IF(IIF)，但是当我在Management Studio中尝试该功能时，它说它是无效功能.

我想从VB.Net(VS2010)中调用它，并且数据库是动态创建的，所以我不希望不使用Stored Proc.除非我可以轻松地动态创建它(以前从未做过，但是我怀疑这是可能的).

建议/评论/建议...全部欢迎.

在此先感谢.

I''m trying to write some code that will grab all my information in one SQL query but sort it so that there are two groups, and then each group is alphabetical.

My current query (which is close) is:

SELECT *, PATINDEX(''%heartbeat%'', lower(TimerName)) As IsHeartBeat FROM tblTimer ORDER BY IsHeartBeat, TimerName, TimerInterval, TimerPriority

The PATINDEX correctly puts all my heartbeat timers to the end of the result set ... but because it doesn''t return just a 0 or 1, true or false, it orders the heartbeats according to where the word heartbeat is found in the name, rather than alphabetically.

I want an inline IF (IIF) but when I try that in management studio it says it''s an invalid function.

I want to call this from VB.Net (VS2010) and the databases are dynamically created so I would prefer not to use a Stored Proc. unless I can easily create it dynamically (never done that previously but I suspect it''s possible).

Suggestions/Comments/Recommendations ... all welcome.

Thanks in advance.

推荐答案

解决方案(或一种解决方案)是:

The solution (or one solution) is:

SELECT TimerName, TimerInterval, TimerPriority, IsHeartBeat =
CASE
 WHEN PATINDEX('%heartbeat%', lower([TimerName])) > 0 THEN 1
 ELSE 0
 END
 FROM [SEMCDatalogger].[dbo].[tblTimer] ORDER BY IsHeartBeat, TimerName, TimerInterval, TimerPriority

通过使用IsHeartBeat = CASE，然后提供条件...结果恰好是我需要的0或1.

By using IsHeartBeat = CASE and then supplying the conditions ... the results are exactly the 0 or 1 that I need.

这篇关于等效于MS-SQL Server 2005 Express，2005 Full和更高版本的内联IIF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！