Delete and Left Join CTE many times

Question

I need to use CTE many times in my stored procedure, one for delete and three for left join, I marked below. I read that I am not supposed to query CTE more than one time. Could anyone please suggest me how to rewrite the code? Thanks

My SP code:

WITH T1 AS   
(
  SELECT ID, Event, SN, Note
FROM je
WHERE je.Event in ('1601','1602') 
AND DATE(Time) = DATE_ADD(current_date(), INTERVAL -1 DAY)
) 
, T2 AS 
(
    SELECT SELECT ID, Event, SN, Note
      ,ROW_NUMBER() OVER (PARTITION BY ID, case when Note like '%@%' and Event='1602' then 'Y' else 'N' end ORDER BY SN DESC) AS Sort
      ,ROW_NUMBER() OVER (PARTITION BY ID, case when Note like '%@%' then 'Y' else 'N' end ORDER BY SN DESC) AS SortI1
    FROM T1
)
DELETE * FROM A Where ID in (SELECT ID from T2)  --#1

INSERT INTO A(
.
.
)
SELECT 
.
.
FROM G
Left Join (SELECT DISTINCT ID, Event FROM T2) k1 --#2
on G.ID=k1.ID
Left Join (SELECT ID, Event, Note like '%@%' FROM T2 where Event='1602') k2 --#3
on G.ID=k2.ID
Left Join (SELECT ID, Event, Note not like '%@%' FROM T2 where Event in ('1601','1602')) k3 --#4
on G.ID=k3.ID
WHERE k1.Event in ('1601','1602')
;

Answer 1

In Microsoft Sql-Server, you can select results into a temporary alias instead of CTE and just continue to use as long as needed. Since TEMP tables are available for the duration of the current session, that might be all you need. Ex:

SELECT 
      ID, 
      Event, 
      SN, 
      Note
   into 
      -- the # denotes a TEMP table
      #T1
   FROM 
      je
   WHERE 
          je.Event in ('1601','1602') 
      AND DATE(Time) = DATE_ADD(current_date(), INTERVAL -1 DAY);


SELECT 
      ID, 
      Event, 
      SN, 
      Note,
      ROW_NUMBER() OVER (PARTITION BY ID, 
                       case when Note like '%@%' and Event='1602' 
                            then 'Y' else 'N' end ORDER BY SN DESC) AS Sort,
      ROW_NUMBER() OVER (PARTITION BY ID, 
                       case when Note like '%@%' 
                            then 'Y' else 'N' end ORDER BY SN DESC) AS SortI1
   into
      #T2
   FROM 
      #T1

DELETE FROM A Where ID in (SELECT ID from #T2)

INSERT INTO A(
   .
   .
)
SELECT 
      .
      .
   FROM 
      G
         Left Join (SELECT DISTINCT ID, Event 
                       FROM #T2) k1 --#2
            on G.ID = k1.ID
         Left Join (SELECT ID, Event, Note like '%@%' 
                       FROM #T2 
                       where Event='1602') k2 --#3
            on G.ID = k2.ID
         Left Join (SELECT ID, Event, Note not like '%@%' 
                       FROM #T2 
                       where Event in ('1601','1602')) k3 --#4
            on G.ID = k3.ID
   WHERE 
      k1.Event in ('1601','1602');

FEEDBACK.

The DELETE * would fail as that is not even valid... it should just be DELETE FROM TABLE WHERE ...

As for using the TEMP tables vs CTE, I've used them regularly for years and never had a problem.

If your underlying query is invalid, then no matter how you write your queries, it would fail. How I would resolve would be to start SSMS and open a sql query dialog.

Run the first query into #t1, then do select * from #t1 to make sure it looks as you expect. Then (since you are still in the same session, run the query for #t2, and then select * to make sure that works.

Next, run the DELETE FROM where 1=2 and [your criteria] just to make sure the syntax is correct, then you can remove the 1=2 and clause (which will always be false and prevent any actual deletions to occur, just making sure the syntax is correct).

Then do the select from G, left join to only the first instance on its criteria. Make sure it works. Then add second, and finally third. The temp #t1 and #t2 will still be there while you are working it out.

Once it is all correct, you can put into a stored procedure, but will need to be parameterized somehow as you appear to be looking for only specific events.