DECLARE @list_klaim TABLE ( no_klaim VARCHAR(11))

INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060081')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060116')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060148')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060151')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060211')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060250')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060287')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060050')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060158')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060066')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060072')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060080')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060084')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060132')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060134')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060164')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060125')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060207')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.060339')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062358')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062370')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062487')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062357')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062546')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062548')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062573')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062537')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062538')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062553')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062560')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062639')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062705')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062604')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.062668')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064820')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064550')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064971')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064536')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064557')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064558')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064645')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064600')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064621')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064638')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064780')
INSERT INTO @list_klaim ( no_klaim ) VALUES ('2015.064814')


DECLARE @hasil TABLE ( 
                  no_klaim VARCHAR(11),
                  no_proposal_lama VARCHAR(9),
                  no_peserta_lama VARCHAR(12),
                  no_proposal_baru VARCHAR(9),
                  no_peserta_baru VARCHAR(12),
                  nama VARCHAR(30),
                  tgl_lahir DATE
               )

DECLARE @no_klaim VARCHAR(11)

DECLARE list_cursor CURSOR FOR  
SELECT no_klaim
FROM @list_klaim 

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_klaim  

WHILE @@FETCH_STATUS = 0  
BEGIN 
  
  DECLARE
    @no_peserta_lama VARCHAR(12),
    @no_peserta_baru VARCHAR(12),
    @no_proposal_baru VARCHAR(9),
    @no_proposal_lama VARCHAR(9),
    @nama VARCHAR(30),
    @tgl_lahir DATE,
    @id_produk_lama VARCHAR(10),
    @id_produk_baru VARCHAR(10)
   
  SELECT
    @no_peserta_lama = No_Peserta,
    @no_proposal_lama = No_Proposal,
    @id_produk_lama = Id_Produk
  FROM
    dbo.Klaim_Peserta
  WHERE 
    No_Klaim = @no_klaim
    AND Status = 'G'
  IF (@@RowCount = 0) -- jika klaim tidak ditemukan
    PRINT 'No Klaim: ' + @no_klaim + ' tidak ditemukan.'
  ELSE  -- jika klaim ditemukan
    BEGIN

      -- cek & ambil data dari tabel mapping
      SELECT DISTINCT
        @no_proposal_baru = No_Proposal_Baru,
        @no_peserta_baru = No_Peserta_Baru,
        @nama = Nama,
        @tgl_lahir = Tgl_Lahir
      FROM
        dbo.temp_prib_mapping_peserta_mugirekso
      WHERE
        No_Peserta_Lama = @no_peserta_lama
      IF (@@RowCount = 1) -- jika ditemukan dan hanya ada 1, berarti oke
        BEGIN
          
          -- proses utama (update)
          UPDATE
            dbo.Klaim_Peserta
          SET
            No_Proposal = @no_proposal_baru,
            No_Peserta = @no_peserta_baru,
            No_Santunan = LEFT(@no_peserta_baru,11) + RIGHT(@no_peserta_lama,1)
          WHERE
            No_Klaim = @no_klaim
            AND No_Proposal = '2014.0249'
          IF (@@Error = 0) AND (@@RowCount <> 0)  -- jika tidak ada error pada saat update, dan ada row
            BEGIN
            
              -- insert ke tabel hasil
              INSERT INTO @hasil
                      (no_klaim,
                       no_proposal_lama,
                       no_peserta_lama,
                       no_proposal_baru,
                       no_peserta_baru,
                       nama,
                       tgl_lahir)
              VALUES
                      (@no_klaim, -- no_klaim - varchar(11)
                       @no_proposal_lama, -- no_proposal_lama - varchar(9)
                       @no_peserta_lama, -- no_peserta_lama - varchar(12)
                       @no_proposal_baru, -- no_proposal_baru - varchar(9)
                       @no_peserta_baru, -- no_peserta_baru - varchar(12)
                       @nama, -- nama - varchar(30)
                       @tgl_lahir  -- tgl_lahir - date
                       )

              -- proses update id produk
              UPDATE
                kp
              SET
                kp.Id_Produk = CASE
                                 WHEN pp.Id_Produk IS NULL THEN pp2.Id_Produk
                                 ELSE pp.Id_Produk
                               END
              FROM
                dbo.Klaim_Peserta AS kp LEFT JOIN
                dbo.Peserta_Produk AS pp ON kp.No_Proposal = pp.No_Proposal AND kp.No_Peserta = pp.No_Peserta AND kp.Sub_Kelompok = pp.Sub_Kelompok INNER JOIN
                ( SELECT
                    No_Proposal,
                    No_Peserta,
                    Id_Produk,
                    Sub_Kelompok
                  FROM
                    dbo.Peserta_Produk
                  WHERE
                    No_Proposal = @no_proposal_baru
                    AND No_Peserta LIKE '%0'
                ) AS pp2 ON kp.No_Proposal = pp2.No_Proposal AND SUBSTRING(kp.No_Peserta,1,10) = SUBSTRING(pp2.No_Peserta,1,10) AND kp.Sub_Kelompok = pp2.Sub_Kelompok
              WHERE
                kp.No_Proposal = @no_proposal_baru
                AND kp.Status = 'G'
                AND kp.No_Klaim = @no_klaim
              
            END
          ELSE  -- jika ada error pada saat update
            PRINT 'Proses UPDATE error. No Klaim: ' + @no_klaim
        END
      ELSE  -- jika di tabel mapping tidak ditemukan peserta tsb atau ditemukan lebih dari 1 no_peserta yg baru
        PRINT 'Error peserta. No Klaim: ' + @no_klaim    
    END
  
  FETCH NEXT FROM list_cursor INTO @no_klaim  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 


SELECT
  *
FROM
  @hasil