why can't I access my CTE after I used it once?

Question

My stored procedure looks like:

WITH MYCTE(....) AS  ( ... )  UPDATE ... (using my CTE)    DELETE ( using my CTE)  <---  says the object, my CTE, doesn't exist 

Can I only use it once?

Answer 1

In your example code, the CTE only persists for the UPDATE. If you need it to last longer, consider populating a #tempTable or @tableVariable with it, and then UPDATE and DELETE from those.

You may also augment your UPDATE to use an OUTPUT clause, like the following, so you can capture the affected rows. And use them in the DELETE, like here:

set nocount on DECLARE @Table     table (PK int, col1 varchar(5)) DECLARE @SavedPks  table (PK int)  INSERT INTO @Table VALUES (1,'g') INSERT INTO @Table VALUES (2,'g') INSERT INTO @Table VALUES (3,'g') INSERT INTO @Table VALUES (4,'g') INSERT INTO @Table VALUES (5,'x') INSERT INTO @Table VALUES (6,'x') set nocount off  ;WITH MYCTE AS  (   SELECT PK, col1 FROM @Table ) UPDATE MYCTE     SET col1='xyz'     OUTPUT INSERTED.PK         INTO @SavedPks     WHERE col1='g'  SELECT 'A',* FROM @Table  DELETE @Table     WHERE PK IN (SELECT PK  from @SavedPks)  SELECT 'B',* FROM @Table 

OUTPUT:

(4 row(s) affected)      PK          col1 ---- ----------- ----- A    1           xyz A    2           xyz A    3           xyz A    4           xyz A    5           x A    6           x  (6 row(s) affected)  (4 row(s) affected)       PK          col1 ---- ----------- ----- B    5           x B    6           x  (2 row(s) affected) 

Answer 2

Yep, the WITH MYCTE clause is not creating a permanent object to use in multiple queries afterwards: it's only modifying the one query you're adding that clause to! If you need very different functonality, consider, instead, using views...