tsql - variable - Es posible ACTUALIZAR y SELECCIONAR en instrucción única usando la cláusula OUTPUT con CTE que contiene JOIN?

uso de output sql server

Question

Tenemos una serie de procedimientos almacenados que BizTalk utiliza para buscar n filas de una cola (y una tabla unida) y actualizar el estado de esos elementos al mismo tiempo.

Estoy tratando de modificar estas consultas para eliminar el uso de variables de tabla y en su lugar hacer el trabajo en una sola declaración. He tenido éxito con algunos, pero este ejemplo particular es complicado porque hay una join en el CTE, y quiero devolver algunas de las columnas unidas aunque no formen parte de la update .

Esto es lo que se me ocurrió:

;with q as
(
    select top (@FetchCount) 
        iq.InQueueId,
        itk.[Message],
        iq.PatNo,
        iq.DistrictNo,
        itk.Interaction,
        iq.[Status]
    from
        InQueue iq
        join Itk on iq.InQueueId = itk.InQueueId
        join [Endpoint] e on iq.[Endpoint] = e.EndpointId
        join EndpointName en on en.EndpointNameId = e.Name
    where
        en.Name = ''XYZ''
        and iq.[Status] = @StatusNew
    order by
        iq.InQueueId
)
update
    q
set
    [Status] = @StatusSelected
output
    inserted.InQueueId as [Id],
    inserted.[Message] as [Msg],
    inserted.DistrictNo,
    inserted.Interaction

Esto falla inmediatamente con el siguiente error:

La referencia de columna "insert.Message" no está permitida porque hace referencia a una tabla base que no se está modificando en esta instrucción.

Claramente, esto se debe a que las columnas Message e Interaction no pueden devolverse como parte del conjunto inserted , porque están en una tabla diferente y, por lo tanto, no se actualizaron.

Así que intenté cambiar la cláusula de output a:

output
    inserted.InQueueId as [Id],
    q.[Message] as [Msg],
    inserted.DistrictNo,
    q.Interaction

Esto falla con el error:

El identificador de varias partes "q.Message" no se pudo enlazar.

¿Es posible lograr esto sin reescribir la consulta para usar tablas temporales o variables de tabla?

Answer 1

Puede usar el conjunto deleted en la cláusula de salida para hacer referencia a columnas en su CTE en tablas que no se actualizan

p.ej

output
    inserted.InQueueId as [Id],
    deleted.[Message] as [Msg],
    inserted.DistrictNo,
    deleted.Interaction

Los conjuntos deleted e inserted en una update también podrían considerarse como antes y después, aunque la terminología aquí coincide con delete...output e insert...output