Cannot use CTE with Exists Clause

Apparently the syntax for the CTE does not work inside an EXISTS function. The solution, however, is very simple; Just use a derived table instead. {Oh yeah forgot that before CTEs we use to use derived tables instead.}

-- Sorry this code doesn't work due to CTE in EXISTS Function
IF EXISTS(
    WITH LargeDepartmentNewHires AS (
        SELECT
              HREDH.DepartmentID
        FROM
            HumanResources.EmployeeDepartmentHistory HREDH
        WHERE
            HREDH.StartDate BETWEEN '1/1/1999' AND '12/31/1999'
        GROUP BY
              HREDH.DepartmentID
             , YEAR(HREDH.StartDate)
        HAVING
              COUNT(*) >=10
        )
    SELECT
          HRE.*
    FROM
        HumanResources.Employee HRE
            INNER JOIN
        HumanResources.EmployeeDepartmentHistory HREDH
            ON HRE.EmployeeID = HREDH.EmployeeID
            INNER JOIN
        LargeDepartmentNewHires LDNH
            ON HREDH.DepartmentID = LDNH.DepartmentID
    )
    BEGIN
        PRINT 'Yes it exists'
    END
ELSE
    BEGIN
        PRINT 'Does NOT exists'
    END

Use a derived table instead of a CTE

--This does work
IF EXISTS(
    SELECT
          HRE.*
    FROM
        HumanResources.Employee HRE
            INNER JOIN
        HumanResources.EmployeeDepartmentHistory HREDH
            ON HRE.EmployeeID = HREDH.EmployeeID
            INNER JOIN
        (
        SELECT
              HREDH.DepartmentID
        FROM
            HumanResources.EmployeeDepartmentHistory HREDH
        WHERE
            HREDH.StartDate BETWEEN '1/1/1999' AND '12/31/1999'
        GROUP BY
              HREDH.DepartmentID
             , YEAR(HREDH.StartDate)
        HAVING
              COUNT(*) >=10
        ) LDNH --LargeDepartmentNewHires
            ON HREDH.DepartmentID = LDNH.DepartmentID
    )
    BEGIN
        PRINT 'Yes it exists'
    END
ELSE
    BEGIN
        PRINT 'Does NOT exists'
    END

If your CTE is a hierarchical query then there isn't much you can do inside the exist statement. However, remember what and how the EXISTS function works. Its purpose is to return a true as soon as the containing query returns any rows if any exist at all. To mimic this functionality rewrite the query to return a count of the rows would have been found in the EXISTS function. If the count is not 0 then it the true that the rows exist. This isn't the most efficient but it is a valid solution. Keep in mind to optimize performance return as little data as possible.

DECLARE @ReturnedRows    INT

;
WITH LargeDepartmentNewHires AS (
    SELECT
          HREDH.DepartmentID
    FROM
        HumanResources.EmployeeDepartmentHistory HREDH
    WHERE
        HREDH.StartDate BETWEEN '1/1/1999' AND '12/31/1999'
    GROUP BY
          HREDH.DepartmentID
         , YEAR(HREDH.StartDate)
    HAVING
          COUNT(*) >=10
    )
SELECT
      @ReturnedRows = COUNT(*)
FROM
    HumanResources.Employee HRE
        INNER JOIN
    HumanResources.EmployeeDepartmentHistory HREDH
        ON HRE.EmployeeID = HREDH.EmployeeID
        INNER JOIN
    LargeDepartmentNewHires LDNH
        ON HREDH.DepartmentID = LDNH.DepartmentID

IF @ReturnedRows != 0
    BEGIN
        PRINT 'Yes it exists'
    END
ELSE
    BEGIN
        PRINT 'Does NOT exists'
    END

Comments

[Riley Major]

--Another option is to use the CTE-based query to set a variable, and then check the variable.

DECLARE @foo bit;
SET @foo = 0;

WITH ShouldBeEmpty AS
(
SELECT 1 AS AnyCol
--WHERE 1 = 0
WHERE 1 = 1
)
SELECT TOP 1 @foo = 1
FROM ShouldBeEmpty

IF @foo = 1
BEGIN
SELECT 'It exists!'
END