We have several stored procedures that BizTalk uses to retrieve n rows from a queue (and a joined table) and update the state of these elements at the same time.
I am trying to modify these queries to remove the use of table variables and instead do the work in a single expression. I managed with some, but this specific example is complicated because there is a join in the CTE, and I want to return some of the joined columns, even if they were not part of the update .
Here is what I came up with:
;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
This does not immediately execute with the following error:
The reference to the column "insert.Message" is not allowed because it refers to a base table that does not change in this expression.
Clearly, this is because the Message and Interaction columns cannot be returned as part of the inserted set because they are in a different table and therefore have not been updated.
So, I tried changing the output clause to:
output inserted.InQueueId as [Id], q.[Message] as [Msg], inserted.DistrictNo, q.Interaction
Error with error:
An identifier with multiple parts of q.Message cannot be associated.
Is it possible to achieve this without rewriting the query in order to use temporary tables or variable tables?