-1

I want to do this :

WITH temp (f1, f2, ...)
BEGIN
    SELECT * FROM temp WHERE <condition 1>
    SELECT * FROM temp WHERE <condition 2>
END

but SQL Server raise error... is there any solution to do this ?

Atzi
  • 457
  • 1
  • 6
  • 16

2 Answers2

1

You can define multiple CTEs separated with a comma , something like this.....

;WITH temp1 (f1, f2, ...) AS 
(    
    SELECT * FROM temp WHERE <condition 1>
 ), 
 Temp2 (f1, f2, ...) AS
 (
    SELECT * FROM temp WHERE <condition 2>
  )
SELECT ......

Or if you want combine results returned from both queries you can do this in One cte something like this....

;WITH temp1 (f1, f2, ...) AS 
(    
  SELECT * FROM temp WHERE <condition 1>
  UNION ALL
  SELECT * FROM temp WHERE <condition 2>
 )
SELECT ......
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • NO, I want : ;WITH temp1 (f1, f2, ...) AS ( SELECT * FROM temp WHERE --UNION ALL --SELECT * FROM temp WHERE ) SELECT ...... and SELECT ...... – Atzi Sep 09 '14 at 23:19
  • 1
    One CTE or Two CTEs if defined together must me consumed in the following statement (UPDATE/INSERT/SELECT). These two following selects you have shown in your comment can be nested or Union but you cannot have to separate selects. This is by syntax , if you do need to write a query like and later select twice from that, then you have only two choices , a Temp table or a Table variable. but not cte . I hope this helps. – M.Ali Sep 09 '14 at 23:24
0

you can use a UNION ALL to merge both datasets

WITH temp AS (select * from mytable)
   SELECT * FROM temp WHERE <condition 1>
   UNION ALL
   SELECT * FROM temp WHERE <condition 2>
Smog
  • 625
  • 6
  • 19