CASE WHEN EXISTS语句未正确显示预期Ongoing状态求助

我太懂你这种糟心感受了——明明确认有数据库的is_trustworthy_on设为1，结果执行语句后状态却显示成了Completed，而非预期的Ongoing，而且同款逻辑在其他语句里都正常，这次偏偏掉链子。咱们来一步步把问题揪出来。

先看你写的这段代码：

declare @STIG varchar(255)
SET @STIG = 'MS SQL Server 2016 Database'
declare @STIG1 varchar(255)
SET @STIG1 = 'MS SQL Server 2016 Instance'
DECLARE @VID2 varchar(20)
set @VID2 = 'V-71'
CREATE TABLE #temp2 ([DatabaseName] varchar(100), [DatabaseOwner] varchar(100), [is_trustworthy_on] varchar(5))
INSERT INTO [#temp2] (DatabaseName, DatabaseOwner, is_trustworthy_on)
SELECT name as DatabaseName, SUSER_SNAME(owner_sid) AS DatabaseOwner, is_trustworthy_on FROM sys.databases
SELECT @@servername as [HostName], @VID2 as [V-ID], @STIG AS [STIG],
(CASE WHEN EXISTS (
SELECT is_trustworthy_on FROM #temp2 WHERE #temp2.is_trustworthy_on = 0)
Then CAST('Completed' AS varchar(12))
ELSE CAST ('Ongoing' AS varchar(12)) END) as [Status]
drop table #temp2

问题根源：逻辑判断完全搞反了！ #

你当前的CASE条件是判断是否存在is_trustworthy_on = 0的记录，如果存在就返回Completed。但SQL Server的系统库（比如master、model）默认is_trustworthy_on就是0，所以你的临时表#temp2里必然有这类记录，条件直接成立，自然就返回Completed了——完全忽略了有没有值为1的记录。

而你的实际需求是：当存在is_trustworthy_on = 1的记录时，显示Ongoing，否则才显示Completed。

修正后的代码 #

把EXISTS的判断条件和CASE的分支逻辑反过来就搞定了，另外还有个小细节要注意：

declare @STIG varchar(255)
SET @STIG = 'MS SQL Server 2016 Database'
declare @STIG1 varchar(255)
SET @STIG1 = 'MS SQL Server 2016 Instance'
DECLARE @VID2 varchar(20)
set @VID2 = 'V-71'
CREATE TABLE #temp2 ([DatabaseName] varchar(100), [DatabaseOwner] varchar(100), [is_trustworthy_on] varchar(5))
INSERT INTO [#temp2] (DatabaseName, DatabaseOwner, is_trustworthy_on)
SELECT name as DatabaseName, SUSER_SNAME(owner_sid) AS DatabaseOwner, is_trustworthy_on FROM sys.databases
SELECT @@servername as [HostName], @VID2 as [V-ID], @STIG AS [STIG],
(CASE WHEN EXISTS (
SELECT is_trustworthy_on FROM #temp2 WHERE #temp2.is_trustworthy_on = '1') -- 因为字段是varchar类型，这里加上单引号更稳妥
Then CAST('Ongoing' AS varchar(12)) -- 存在值为1的记录时返回Ongoing
ELSE CAST ('Completed' AS varchar(12)) END) as [Status] -- 所有记录都是0时才返回Completed
drop table #temp2

这样修改后，只要你的数据库里有任何一个is_trustworthy_on为1的记录，就会返回你预期的Ongoing状态了。

内容的提问来源于stack exchange，提问作者J T