there's two procedures that make it a bit complicated to convert to a set based operaiton, becaus3e both procs look like they fiddle with one row at a time:

[dbo].[UpdateFrequencyCheckNote]

[dbo].[CreateFrequencyCheckNote]

based on the name , i guess they insert or update a table...if it's the same table, unless there are some complex calculations in them, i think they can be replaced too; the devil is in the details.

i think it would be replaced by two set based operations, based on whatever table(s) those procs fiddle with(? OrderEvent from the comment?)

I'd only be guessing at this point, but i wanted to at least provide a somewhat intellgent example;

this will not pass syntax, but it kind of shows how you'd update from multiple sources:

UPDATE MyTargetTable

SET MyTargetTable.NoteText = OneOfTheSubQueryTables.NoteText,

MyTargetTable.NoteId = OneOfTheSubQueryTables.NoteId,

MyTargetTable.LastPerformedDate = OneOfTheSubQueryTables.LastPerformedDate,

MyTargetTable.FrequencyCheckNoteId = OneOfTheSubQueryTables.FrequencyCheckNoteId

--SANITY Check: run this SELECT to confirm we would updat ethe right stuff!

--SELECT *

FROM dbo.OrderEvent MyTargetTable

INNER JOIN dbo.FrequencyCheckNotes ON NoteOrderEventId = @OrderEventId

INNER JOIN (SELECT ColumnList FROM dbo.rvw_OrderEventOrderCodes as t1

inner join dbo.[Order] as t2 on t1.OrderId = t2.id inner join

dbo.Patient as t3 on t2.PatientId = t3.id

where t1.ScheduledDueDate > getdate() and t1.OrderCode=@OrderCode and t1.[Status]=2 and t3.PatientId = @PatientId