/* -- START: PEMBUATAN TABEL -- */
CREATE TABLE customer (
   kd_customer CHAR(10) CONSTRAINT pk_kdpelanggan PRIMARY KEY,
   nm_customer VARCHAR(25),
   alamat VARCHAR(50),
   telepon VARCHAR(15),
   contact VARCHAR(50),
   kota VARCHAR(50)
   );

DROP TABLE customer;
/* -- END: PEMBUATAN TABEL -- */

/* -- START: PENGINPUTAN DATA -- */
INSERT INTO customer ( kd_customer, nm_customer, alamat, telepon, contact, kota ) VALUES
   ( 'A0001', 'Yadi Purdianto', 'Jl. Cicadas No. 34 Bandung', '082130996113', 'yadipurdianto@gmail.com', 'Bandung' ),
   ( 'A0002', 'Vania Putrika', 'Jl. Garuda No. 71 Bandung', '08821456382', 'vnaiaputrika@gmail.com', 'Bandung' ),
   ( 'A0003', 'Xena Devi Zerlinda', 'Jl. Garuda No. 71 Bandung', '085720126852', 'devi.xena@gmail.com', 'Bandung' ),
   ( 'A0004', 'Nanang Suptriatna', 'Jl. Leuwigajah No 12A Cimahi', '088015254203', 'nanangsanud21@gmail.com', 'Cimahi' ),
   ( 'A0005', 'Saripudin', 'Jl. Babakan Hilir No. 3 Cianjur', '089621354683', 'sarip@gmail.com', 'Cianjur' ),
   ( 'A0006', 'Komarudin Saepuloh', 'Komp. Permata Indah Blok A3 Cimahi', '0838216546873', 'komar.sae@gmail.com', 'Cimahi' );

SELECT * FROM customer;
/* -- END: PENGINPUTAN DATA -- */

/* -- START: STORE PROCEDURE DENGAN LOOPING -- */
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_NoPelanggan AS
   DECLARE @vKd_customer CHAR(5)
   DECLARE @vNm_customer VARCHAR(25)
   DECLARE @vAlamat VARCHAR(50)
   DECLARE @vTelepon VARCHAR(15)
   DECLARE @vContact VARCHAR(50)
   DECLARE @vKota VARCHAR(50)
   DECLARE @vNoUrut INT

   DECLARE @TempPelanggan TABLE (
      NoUrut INT,
      kd_customer CHAR(10) DEFAULT NULL,
      nm_customer VARCHAR(25) DEFAULT NULL,
      alamat VARCHAR(50) DEFAULT NULL,
      telepon VARCHAR(15) DEFAULT NULL,
      contact VARCHAR(50) DEFAULT NULL,
      kota VARCHAR(50) DEFAULT NULL )

   DECLARE cPelanggan CURSOR FOR SELECT kd_customer, nm_customer, alamat, telepon, contact, kota FROM customer
   OPEN cPelanggan
   FETCH NEXT FROM cPelanggan INTO
   @vKd_customer, @vNm_customer, @vAlamat, @vTelepon, @vContact, @vKota
   SET @vNoUrut = 0 WHILE (@@FETCH_STATUS = 0)
      BEGIN
         SET @vNoUrut = @vNoUrut + 1
         INSERT INTO @TempPelanggan ( NoUrut, kd_customer, nm_customer, alamat, telepon, contact, kota ) VALUES
            ( @vNoUrut, @vKd_customer, @vNm_customer, @vAlamat, @vTelepon, @vContact, @vKota )
         FETCH NEXT FROM cPelanggan INTO
            @vKd_customer, @vNm_customer, @vAlamat, @vTelepon, @vContact, @vKota
      END
      CLOSE cPelanggan
      DEALLOCATE cPelanggan
      SELECT NoUrut, kd_customer, nm_customer, alamat, telepon, contact, kota FROM @TempPelanggan
   GO
   SET QUOTED_IDENTIFIER OFF
   GO
   SET ANSI_NULLS ON
   GO

EXECUTE sp_NoPelanggan;
/* -- END: STORE PROCEDURE DENGAN LOOPING -- */