I have write a cursor in which i am getting values from a table using cursor and updating these values in another table . While testing i have analysed that values are storing in table and update is also working because sql server message shows that 1 row has been update but the values from cursor are not being update in that table I am confused and not able to get any answers
DECLARE
@R_AGE$PATIENT_SEQ_NUM numeric,
@R_AGE$PATIENT_TOTAL_BAL DECIMAL(18,2),
@R_AGE$INS_TOTAL_BAL DECIMAL(18,2),
@R_AGE$PAT_CURRENT_AMOUNT DECIMAL(18,2),
@R_AGE$PAT_ABOVE_30 DECIMAL(18,2),
@R_AGE$PAT_ABOVE_60 DECIMAL(18,2),
@R_AGE$PAT_ABOVE_90 DECIMAL(18,2),
@R_AGE$PAT_ABOVE_120 DECIMAL(18,2),
@R_AGE$INS_CURRENT_AMOUNT DECIMAL(18,2),
@R_AGE$INS_ABOVE_30 DECIMAL(18,2),
@R_AGE$INS_ABOVE_60 DECIMAL(18,2),
@R_AGE$INS_ABOVE_90 DECIMAL(18,2),
@R_AGE$INS_ABOVE_120 DECIMAL(18,2)
DECLARE
GET_AGE CURSOR LOCAL FOR
SELECT
T_PATIENT_AMOUNT_AGE.PATIENT_SEQ_NUM,
T_PATIENT_AMOUNT_AGE.PATIENT_TOTAL_BAL,
T_PATIENT_AMOUNT_AGE.INS_TOTAL_BAL,
T_PATIENT_AMOUNT_AGE.PAT_CURRENT_AMOUNT,
T_PATIENT_AMOUNT_AGE.PAT_ABOVE_30,
T_PATIENT_AMOUNT_AGE.PAT_ABOVE_60,
T_PATIENT_AMOUNT_AGE.PAT_ABOVE_90,
T_PATIENT_AMOUNT_AGE.PAT_ABOVE_120,
T_PATIENT_AMOUNT_AGE.INS_CURRENT_AMOUNT,
T_PATIENT_AMOUNT_AGE.INS_ABOVE_30,
T_PATIENT_AMOUNT_AGE.INS_ABOVE_60,
T_PATIENT_AMOUNT_AGE.INS_ABOVE_90,
T_PATIENT_AMOUNT_AGE.INS_ABOVE_120
FROM T_PATIENT_AMOUNT_AGE
where patient_seq_num=4366999
OPEN GET_AGE
FETCH next from GET_AGE
INTO
@R_AGE$PATIENT_SEQ_NUM,
@R_AGE$PATIENT_TOTAL_BAL,
@R_AGE$INS_TOTAL_BAL,
@R_AGE$PAT_CURRENT_AMOUNT,
@R_AGE$PAT_ABOVE_30,
@R_AGE$PAT_ABOVE_60,
@R_AGE$PAT_ABOVE_90,
@R_AGE$PAT_ABOVE_120,
@R_AGE$INS_CURRENT_AMOUNT,
@R_AGE$INS_ABOVE_30,
@R_AGE$INS_ABOVE_60,
@R_AGE$INS_ABOVE_90,
@R_AGE$INS_ABOVE_120
while @@FETCH_STATUS = 0
BEGIN
UPDATE PATIENT_PROF
SET
PATIENT_TOTAL_BAL = @R_AGE$PATIENT_TOTAL_BAL,
INS_TOTAL_BAL = @R_AGE$INS_TOTAL_BAL,
PATIENT_CURRENT_AMT_DUE = @R_AGE$PAT_CURRENT_AMOUNT,
ABOVE_30 = @R_AGE$PAT_ABOVE_30,
ABOVE_60 = @R_AGE$PAT_ABOVE_60,
ABOVE_90 = @R_AGE$PAT_ABOVE_90,
ABOVE_120 = 5000,
INS_CURRENT_AMT_DUE = @R_AGE$INS_CURRENT_AMOUNT,
INS_ABOVE_30 = @R_AGE$INS_ABOVE_30,
INS_ABOVE_60 = @R_AGE$INS_ABOVE_60,
INS_ABOVE_90 = @R_AGE$INS_ABOVE_90,
INS_ABOVE_120 = @R_AGE$INS_ABOVE_120
WHERE PATIENT_PROF.SEQ_NUM = 4366999
FETCH next from GET_AGE
INTO
@R_AGE$PATIENT_SEQ_NUM,
@R_AGE$PATIENT_TOTAL_BAL,
@R_AGE$INS_TOTAL_BAL,
@R_AGE$PAT_CURRENT_AMOUNT,
@R_AGE$PAT_ABOVE_30,
@R_AGE$PAT_ABOVE_60,
@R_AGE$PAT_ABOVE_90,
@R_AGE$PAT_ABOVE_120,
@R_AGE$INS_CURRENT_AMOUNT,
@R_AGE$INS_ABOVE_30,
@R_AGE$INS_ABOVE_60,
@R_AGE$INS_ABOVE_90,
@R_AGE$INS_ABOVE_120
END
CLOSE GET_AGE
DEALLOCATE GET_AGE
CodePudding user response:
I'm not clear why you were having trouble, but this really doesn't need a cursor (almost nothing does, except for a few very niche scenarios, they are slow and difficult to code).
You can just a straight joined update
UPDATE pp
SET
PATIENT_TOTAL_BAL = pae.PATIENT_TOTAL_BAL,
INS_TOTAL_BAL = pae.INS_TOTAL_BAL,
PATIENT_CURRENT_AMT_DUE = pae.PAT_CURRENT_AMOUNT,
ABOVE_30 = pae.PAT_ABOVE_30,
ABOVE_60 = pae.PAT_ABOVE_60,
ABOVE_90 = pae.PAT_ABOVE_90,
ABOVE_120 = 5000,
INS_CURRENT_AMT_DUE = pae.INS_CURRENT_AMOUNT,
INS_ABOVE_30 = pae.INS_ABOVE_30,
INS_ABOVE_60 = pae.INS_ABOVE_60,
INS_ABOVE_90 = pae.INS_ABOVE_90,
INS_ABOVE_120 = pae.INS_ABOVE_120
FROM PATIENT_PROF pp
JOIN T_PATIENT_AMOUNT_AGE pae ON pae.patient_seq_num = pp.SEQ_NUM
WHERE pp.SEQ_NUM = 4366999;
You can remove the WHERE
to run this on the whole table.