Comperatore Craig wrote:
> I changed my declare statement to:

You're syntax is incorrect check
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp>

You need a FOR statement and have to loss the outer parens around your
select to get the syntax right. I suggest FORWARD_ONLY SCROLL_LOCKS
cursor mode options too for your usage.

Also, you may not specify any columns from alias Z in the FOR UPDATE
clause. Z's fields are derived values, you can not SET them (this was
likely the source of your earlier read_only error message). The columns
you specify in FOR UPDATE OF should be the same columns you target
(assign) with the SET clause of your UPDATE... FOR CURRENT OF.

Here's what it should look like:

DECLARE @inactivate_DI_assign CURSOR
FORWARD_ONLY SCROLL_LOCKS FOR
SELECT p.*, z.last_discharge
from Patient p
inner join (-- Find last discharge date
select p.patient_id
, max(pa.date_discharged) as last_discharge
from patient p
join patient_assignment pa
on p.patient_id = pa.patient_id
and p.episode_id = pa.episode_id
where pa.patient_id in
(select patient_id from patient_assignment
where date_discharged is not null)
and pa.patient_id not in
(select patient_id from patient_assignment
where date_discharged is null)
and p.case_status = 'A'-- Only active patients
and pa.patient_id <> '00000000' -- Ignore NONCLIENT
group by p.patient_id, p.case_status
having datediff(d,max(pa.date_discharged),getdate()) > 40
) z
on p.patient_id = z.patient_id
where p.case_status = 'A'
and p.patient_id <> '00000000'
FOR UPDATE OF p.case_status, p.case_substatus, p.episode_close_date
,p.user_id, p.entry_chron, p.comments


--
Devin Ben-Hur | President / CTO
eMarket Group, Ltd | http://www.emarket-group.com
Zat.com | http://www.Zat.com
503/445-8028 | mailto:[Email address protected]

At least when a friend shoots you in the face,
you know where he stands.

MS Sql Server LazyDBA home page