sql - 将 IF EXISTS 与 CTE 结合使用

Question

我想检查 CTE 表是否有记录或为空。但对于下面的 SQL，我总是收到错误消息“关键字 'IF' 附近的语法不正确”。现在 ADMISSION_OUTSIDE TABLE 中没有匹配的记录。 SQl 的结果应打印“NOT OK”。谢谢，

WITH ADMISSION_OUTSIDE AS 
(   .....
.....
)

IF EXISTS (SELECT * FROM ADMISSION_OUTSIDE)
PRINT 'OK'
ELSE PRINT 'NOT OK'

Answer 1

来自MSDN

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns

可以这样重写

WITH ADMISSION_OUTSIDE AS 
(   .....
.....
)
SELECT 'OK' WHERE EXISTS (SELECT * FROM ADMISSION_OUTSIDE)
UNION ALL
SELECT 'NOT OK' WHERE NOT EXISTS (SELECT * FROM ADMISSION_OUTSIDE)

这是一个演示

;WITH CTE AS
(
SELECT 1 as a WHERE 1=0
)
SELECT 'OK' WHERE EXISTS (SELECT * FROM CTE)
UNION ALL
SELECT 'NOT OK' WHERE NOT EXISTS (SELECT * FROM CTE)

结果: 不正常

;WITH CTE AS
(
SELECT 1 as a WHERE 1=1
)
SELECT 'OK' WHERE EXISTS (SELECT * FROM CTE)
UNION ALL
SELECT 'NOT OK' WHERE NOT EXISTS (SELECT * FROM CTE)

结果: 确定