使用带有 CTE 的 IF EXISTS [英] Using IF EXISTS with a CTE

问题描述

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

I want to check if a CTE table has record or null. But I always get error message 'Incorrect syntax near the keyword 'IF'' for the SQL below. Now there is no matching record in ADMISSION_OUTSIDE TABLE. The result of the SQl should print 'NOT OK'. Thanks,

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

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

推荐答案

来自 MSDN

CTE 后必须跟单个 SELECT、INSERT、UPDATE 或 DELETE引用部分或全部 CTE 列的语句

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)

结果: OK

这篇关于使用带有 CTE 的 IF EXISTS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！