una - with sql server ejemplo

¿Por qué no puedo acceder a mi CTE después de usarlo una vez?

Question

Mi procedimiento almacenado se parece a:

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

¿Puedo usarlo solo una vez?

Answer 1

CTE no crea nada "real". Son simplemente un elemento de lenguaje , una forma de expresar una expresión de tabla que se utilizará, posiblemente en varias ocasiones, en una declaración. Cuando tu dices

WITH cteFoo AS (select ... from table where ...)
select ... from cteFoo where ...

es solo otra manera de decir

select ... from (select ... from table where ....) as cteFoo where ...

El CTE y las tablas derivadas son muy similares, cualquier consulta que use tablas derivadas se puede reescribir como un CTE, y cualquier CTE no recursivo se puede reescribir como una consulta utilizando tablas derivadas. Personalmente, prefiero el formulario CTE, ya que es más conciso y fácil de leer.

Los CTE permiten que una expresión de tabla utilizada varias veces se declare solo una vez:

 WITH cte AS (select ... from table where ...)
select ...
from cte a join cte b on ...
where ...

Compare esto con la forma de tabla derivada semánticamente similar:

select ...
from (
select ... from table where ...) as a
join (
select ... from table where ...) as b
on ...
where ...

El CTE es claramente más legible. Pero debes entender que las dos formas están produciendo la misma consulta. El formulario CTE podría sugerir que se cree un resultado intermedio y luego la unión se ejecute en el resultado intermedio, pero esto no es cierto. La forma de CTE se compila exactamente en la misma forma que la tabla derivada, lo que deja en claro el hecho de que la expresión de tabla de CTE se ejecuta dos veces .

Answer 2

En su código de ejemplo, el CTE solo persiste para la ACTUALIZACIÓN. Si lo necesita para durar más tiempo, considere llenar un #tempTable o @tableVariable con él, y luego ACTUALIZAR y BORRAR de esos.

También puede aumentar su ACTUALIZACIÓN para usar una cláusula de OUTPUT , como la siguiente, para que pueda capturar las filas afectadas. Y úsalos en el BORRAR, como aquí:

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

SALIDA:

(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 3

Sí, la cláusula WITH MYCTE no está creando un objeto permanente para usar en varias consultas después: ¡solo está modificando la consulta a la que se agrega esa cláusula! Si necesita una funcionalidad muy diferente, considere, en su lugar, usar vistas ...

Answer 4

Una expresión CTE solo es válida en su cuerpo. Si desea utilizarlo en otros lugares, también debe repetir la cláusula WITH .

WITH MYCTE(....) AS ( ... )
UPDATE ... (using my CTE);
-- a semicolon is necessary for statements followed by a CTE declaration
WITH MYCTE(....) AS ( ... )
DELETE ( using my CTE);