SQL WITH in WITH

Question

I want to write a with in with SQL query.

Go some error. Please help:

DECLARE @Start AS DATETIME;
DECLARE @End AS DATETIME;
SET @Start = '2013-04-09';
SET @End = '2013-04-11';
with View_Solidnet_Training as 
(
with View_Solidnet_Training as
(
select  cast(@Start as datetime) DateValue
union all
select DateValue + 1
from View_Solidnet_Training
where DateValue + 1 <= cast(@End as datetime)
)
insert into OBJ_Availability  
select 34, DateValue, 'AM', 2, 'Test' from View_Solidnet_Training;
)
select * from View_Solidnet_Training where PK_Training_ID is not null;

error:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'.

What on earth are you trying to do?!?!? You **cannot** have `DECLARE` and `SET` inside a CTE, and you **cannot** insert a CTE into another table. Please **explain** in plain English what you're trying to do ..... — marc_s, Apr 09 '13 at 09:23
Marc is right - your sample query has too many errors for us to see what you are trying to achieve. Perhaps if you explain your table structure and what you want to get from it, that would help. — Hugh Jones, Apr 09 '13 at 09:25
I changed it already a little. But I must insert data from the view to the table. The middle 'With' works perfect. It insert rows in the table. But in SSIS, this solution works only for one row in the view. So I need to make another loop where I go checking if the ID of the view isn't zero, it must declare the new dates and insert the next row of the view. — user2206834, Apr 09 '13 at 09:29
you cannot nest two operations with CTE - you need a first CTE to combine the data and then do a `INSERT INTO OBJ_Availability SELECT (columns) FROM CTE1` and then you need to create a second, separate (**not** nested!) CTE to select the data again. — marc_s, Apr 09 '13 at 09:34
possible duplicate of [CTE looping query](http://stackoverflow.com/questions/15897537/cte-looping-query) — , Apr 09 '13 at 10:07

score 1 · Answer 1 · answered Apr 09 '13 at 09:40

Try this. This is just sample code. In this code , it is written the CTE withing CTE.

;with CTE1 as (
SELECT Patientid
,Lastname1
,age
,dob
,ROW_NUMBER() OVER (ORDER BY Patientid DESC) AS RowNumber
FROM PTN_PATIENT    
)
,CTE2 AS (
SELECT CTE1.Patientid
,CTE1.Lastname1
,CTE1.age
,CTE1.dob
,CTE1.RowNumber
,DATEDIFF(YEAR,CTE1.dob,GETDATE()) as yearOfservce
FROM Lab_LabDiagOrder INNER JOIN CTE1
ON Lab_LabDiagOrder.Patientid = CTE1.Patientid
WHERE CTE1.RowNumber between 1 and 5
)
SELECT * FROM CTE2;

score 0 · Answer 2 · answered Apr 09 '13 at 09:27

You cannot have DECLARE and SET inside a CTE (Common Table Expression).

DECLARE @Start AS DATETIME;
DECLARE @End AS DATETIME;
SET @Start = '2013-04-09';
SET @End = '2013-04-11';

;WITH View_Solidnet_Training AS
(
    SELECT @Start AS DateValue

    UNION ALL

    SELECT DateValue + 1
    FROM View_Solidnet_Training
    WHERE DateValue + 1 <= @End 
)
SELECT
    34, DateValue, 'AM', 2, 'Test' 
FROM 
    View_Solidnet_Training
-- WHERE 
--    PK_Training_ID IS NOT NULL

I don't know where that PK_Training_ID is supposed to come from - it's nowhere to be found in your code.....

Notes:

you cannot have DECLARE and SET inside a CTE - only SELECT and UNION ALL
the @Start and @End are already declared as DATETIME - there's absolutely no point in casting those to DATETIME again ....
the values from the CTE are available right after the CTE, for exactly one T-SQL statement

SQL WITH in WITH

2 Answers2