Sql-server – Using a CTE in IF EXISTS Query

ctesql server

Is it possible to do something similar to the following in SQL Server 2012?

IF EXISTS (
    WITH DATA AS (
        SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn
        FROM table )
    SELECT *
    FROM DATA
    WHERE rn = 2 )
BEGIN
...
END

I tried using this syntax and received an error. If this is not possible, would using a temp table be the best way to accomplish this?

Best Answer

A CTE can't be used as a subquery. One workaround would be:

IF EXISTS 
(
  SELECT 1 FROM 
  (
    SELECT ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn
    FROM table
  ) AS DATA 
  WHERE rn = 2
)
BEGIN
  ...
END

Another would be:

IF EXISTS (SELECT 1 FROM dbo.table GROUP BY column HAVING COUNT(*) > 1)
BEGIN
  ...
END

Even if your proposed syntax were valid, the EXISTS wouldn't short circuit in that case anyway, I would think (and I suspect that is why you'd want to use it), since the window function must materialize over the full set before rn could be filtered.

Related Question