Updating a cursor based on a #temp table with a cursor

[tjohnsb]

Hello All,

I have posted my code which is lenghty. The problem I am experiencing is that my base table is not being updated. Any suggestions?

declare @Event varchar(3),@F_Name varchar(20),@L_Name varchar(25),
@M_Name varchar(15),@Social varchar(9),@Sex varchar(1),
@Patcomm varchar(11),@MedRecNo varchar(10),@Suffix varchar(3),
@BirthDate varchar(8),@PatientType varchar(1),@Race varchar(1),
@ServDate varchar(8),@ServTime varchar(4),@ServArea varchar(5),
@AttMDid varchar(5),@AttMDName varchar(35),@JHHNonPrefMRN varchar(10),
@BF_Name varchar(20),@BL_Name varchar(25),@BM_Name varchar(15),
@BSocial varchar(9),@BSex varchar(1),@BPatcomm varchar(11),
@BMedRecNo varchar(10),@BSuffix varchar(3),@BBirthDate varchar(8),
@BPatientType varchar(1),@BRace varchar(1),@BServDate varchar(8),
@BServTime varchar(4),@BServArea varchar(5),@BAttMDid varchar(5),
@BAttMDName varchar(35),@BJHHNonPrefMRN varchar(10)

declare HL7_Upd cursor
for Select Event,FirstName,LastName,MiddleInitial,SSN,Sex,
Patcomm,MedRecNo,Suffix,BirthDate,PatType,Race,ServDate,
ServTime,ServArea,AttMDid,AttMDName,JHHNonPrefMRN
from HL7_Upd for update

open HL7_Upd
fetch next from HL7_Upd
into @Event,@F_Name,@L_Name,@M_Name,@Social,@Sex,@Patcomm,
@MedRecNo,@Suffix,@BirthDate,@PatientType,@Race,@ServDate,
@ServTime,@ServArea,@AttMDid,@AttMDName,@JHHNonPrefMRN


while @@fetch_status = 0
begin

SELECT Field1,Field2,Field4,Field5,Field6,Field7,Field10,
Field11,Field12,Field13,Field14,Field15,Field16,Field17,
Field18,Field19,Field20
INTO #tempPatient
FROM sysop.ae_rf24
WHERE Field2 = @MedRecNo

declare Patient_Upd cursor
for Select Field1,Field2,Field4,Field5,Field6,Field7,Field10,
Field11,Field12,Field13,Field14,Field15,Field16,Field17,
Field18,Field19,Field20 from #tempPatient
for update

open Patient_Upd
fetch next from Patient_Upd
into @BPatcomm,@BMedRecNo,@BL_Name,@BF_Name,@BM_Name,
@BSuffix,@BBirthDate,@BSex,@BRace,@BSocial,@BPatientType,
@BServDate,@BServTime,@BServArea,@BAttMDid,@BAttMDName,
@BJHHNonPrefMRN

while @@fetch_status = 0
begin


IF @Patcomm = @BPatcomm

BEGIN

If @L_Name is not null
begin
Insert Into Pnt_Audit
Values (@BL_Name,@L_Name,'Last Name',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field4 = @L_Name where current of Patient_Upd
End

If @F_Name is not null
begin
Insert Into Pnt_Audit
Values (@BF_Name,@F_Name,'First Name',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field5 = @F_Name where current of Patient_Upd
End

If @M_Name is not null
begin
Insert Into Pnt_Audit
Values (@BM_Name,@M_Name,'Middle Name',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field6 = @M_Name where current of Patient_Upd
End

If @Suffix is not null
begin
Insert Into Pnt_Audit
Values (@BSuffix,@Suffix,'Suffix',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field7 = @Suffix where current of Patient_Upd
End

If @Birthdate is not null
begin
Insert Into Pnt_Audit
Values (@BBirthdate,@Birthdate,'Birth Date',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field10 = @Birthdate where current of Patient_Upd
End

If @Sex is not null
begin
Insert Into Pnt_Audit
Values (@BSex,@Sex,'Sex',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field11 = @Sex where current of Patient_Upd
End

If @Race is not null
begin
Insert Into Pnt_Audit
Values (@BRace,@Race,'Race',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field12 = @Race where current of Patient_Upd
End

If @Social is not null
begin
Insert Into Pnt_Audit
Values (@BSocial,@Social,'Social',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field13 = @Social where current of Patient_Upd
End

If @PatientType is not null
begin
Insert Into Pnt_Audit
Values (@BPatientType,@PatientType,'Patient Type',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field14 = @PatientType where current of Patient_Upd
End

If @ServDate is not null
begin
Insert Into Pnt_Audit
Values (@BServDate,@ServDate,'Service Date',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field15 = @ServDate where current of Patient_Upd
End

If @ServTime is not null
begin
Insert Into Pnt_Audit
Values (@BServTime,@ServTime,'Service Time',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field16 = @ServTime where current of Patient_Upd
End

If @ServArea is not null
begin
Insert Into Pnt_Audit
Values (@BServArea,@ServArea,'Service Area',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field17 = @ServArea where current of Patient_Upd
End

If @AttMDid is not null
begin
Insert Into Pnt_Audit
Values (@BAttMDid,@AttMDid,'Physician ID',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field18 = @AttMDid where current of Patient_Upd
End

If @AttMDName is not null
begin
Insert Into Pnt_Audit
Values (@BAttMDName,@AttMDName,'Physician Name',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field19 = @AttMDName where current of Patient_Upd
End

If @JHHNonPrefMRN is not null
begin
Insert Into Pnt_Audit
Values (@BJHHNonPrefMRN,@JHHNonPrefMRN,'Changed MRN',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field20 = @JHHNonPrefMRN where current of Patient_Upd
End

End

Else

BEGIN


If @Birthdate is not null
begin
Insert Into Pnt_Audit
Values (@BBirthdate,@Birthdate,'Birth Date',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field10 = @Birthdate where current of Patient_Upd
End

If @Sex is not null
begin
Insert Into Pnt_Audit
Values (@BSex,@Sex,'Sex',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field11 = @Sex where current of Patient_Upd
End

If @Race is not null
begin
Insert Into Pnt_Audit
Values (@BRace,@Race,'Race',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field12 = @Race where current of Patient_Upd
End

If @Social is not null
begin
Insert Into Pnt_Audit
Values (@BSocial,@Social,'Social',@MedRecNo,@BPatcomm, GetDate())
update #tempPatient set Field13 = @Social where current of Patient_Upd
End



End


fetch next from Patient_Upd
into @BPatcomm,@BMedRecNo,@BL_Name,@BF_Name,
@BM_Name,@BSuffix,@BBirthDate,@BSex,@BRace,
@BSocial,@BPatientType,@BServDate,@BServTime,
@BServArea,@BAttMDid,@BAttMDName,@BJHHNonPrefMRN

End


CLOSE Patient_Upd
DEALLOCATE Patient_Upd

SELECT * FROM #tempPatient

drop table #tempPatient

fetch next from HL7_Upd
into @Event,@F_Name,@L_Name,@M_Name,@Social,@Sex,
@Patcomm,@MedRecNo,@Suffix,@BirthDate,@PatientType,
@Race,@ServDate,@ServTime,@ServArea,@AttMDid,
@AttMDName,@JHHNonPrefMRN

END

close HL7_Upd
deallocate HL7_upd


go

Thank you.

Tanesha

 

[tjohnsb]

I solved my own problem. Amazing what a little bit a sleep and an embarassing post can do.