8

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?

  • From BOL - Guidelines for Creating and Using Common Table Expressions, you cant. Best is to use temp table. – Kin Shah Apr 28 '15 at 18:41
  • Why not IF EXISTS (SELECT * FROM table)? – ypercubeᵀᴹ Apr 28 '15 at 18:41
  • @ypercube The example I provided isn't an exact replica of the query I'm working with - seeing if the tables have rows would not be sufficient in what I'm attempting. I guess it would've been more accurate to say WHERE rn = 2 – Weston Sankey Apr 28 '15 at 18:50
  • 1
    Then you could also say IF EXISTS (SELECT column FROM dbo.table GROUP BY column HAVING COUNT(*)>1) – Aaron Bertrand Apr 28 '15 at 19:07
10

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.

2

I Think you can use code like this:

IF OBJECT_ID('tempdb..#data1') IS NOT NULL
            BEGIN 
                DROP TABLE #data1;
            END;

           CREATE TABLE #data1 (
id INT
)

IF OBJECT_ID('tempdb..#data2') IS NOT NULL
                BEGIN 
                    DROP TABLE #data2;
                END;

CREATE TABLE #data2 (
id INT
)

INSERT INTO #data1
VALUES (1), (2), (3), (4)

INSERT INTO #data2
VALUES (4), (5)

DECLARE @result INT = 0;


;WITH result_set AS (
SELECT id FROM #data1
  UNION 
SELECT id FROM #data2
)
SELECT @result = 1 FROM result_set WHERE id = 5 --6

IF (@result = 1)
BEGIN 
SELECT 'YAHOO'
END 

Condition result can be stored as a variable.

2

Another option is to use a variable:

DECLARE @HasRows bit = 0;

WITH foo as 
(
    ...
)
SELECT TOP(1) @HasRows = 1
FROM foo;

IF @HasRows
BEGIN
    PRINT 'True';
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.