Cursor is storing values in a variable but not updating in another table Sql Server

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

Answer

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.

Advertisement

Answer