Menu
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'
722
An IF is a control flow mechanic that separates statements (or blocks of statements) so you can't declare a CTE and conditionally refer to it on multiple statements.
The clauses like ORDER BY, INTO, OPTION clause with query hints, FOR XML, FOR BROWSE, cannot be used in the CTE query definition. "SELECT DISTINCT", GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries are not allowed in the CTE query definition of a recursive member.
The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
A CTE (common table expression) is a named subquery defined in a WITH clause. You can think of the CTE as a temporary view for use in the statement that defines the CTE. The CTE defines the temporary view's name, an optional list of column names, and a query expression (i.e. a SELECT statement).
From MSDN
A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns
It can be re-written like this
WITH ADMISSION_OUTSIDE AS
( .....
.....
)
SELECT 'OK' WHERE EXISTS (SELECT * FROM ADMISSION_OUTSIDE)
UNION ALL
SELECT 'NOT OK' WHERE NOT EXISTS (SELECT * FROM ADMISSION_OUTSIDE)
Here is a demo
;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)
Result : NOT OK
;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)
Result : OK
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
