我想检查CTE表是否有记录或null.但我总是收到错误信息’关键字’IF’附近的sql语法错误,如下面的sql.现在ADMISSION_OUTSIDE TABLE中没有匹配的记录. sql的结果应该打印’NOT OK’.谢谢,
WITH ADMISSION_OUTSIDE AS ( ..... ..... ) IF EXISTS (SELECT * FROM ADMISSION_OUTSIDE) PRINT 'OK' ELSE PRINT 'NOT OK'
解决方法
从
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)
结果：好的
