SQL Server - Function Autonumber Dengan Kombinasi Tanggal (2018.02.0001)

Assalamu'alaikum wr.wb.
Pada kesempatan ini saya akan memberikan tutorial tentang membuat function autonumber dengan kombinasi tanggal, kode, dan nomor urutan. Kita yang sebagai mahasiswa ataupun pernah menjadi mahasiswa pasti tidak asing lagi dengan NIM. Dimana NIM kita menggunakan kombinasi tahun, kode jurusan dan angka urutan. Itulah yang akan saya bahas kali ini.

Dengan menambah tanggal pada autonumber. Tentu id akan terlihat lebih indah dipandang. Berbeda dengan artikel sebelumnya autonumber dengan angka dan huruf yang mana autonumber yang digunakan disana terkesan standart dan tidak berkelas. Untuk autonumber yang ini akan menggunakan jumlah char yang lebih banyak dari sebelumnya.



Baca Juga

• Lebih Lanjut Tentang SELECT Di MySQL
• Cara Membuat Kursi Kosong Dan Isi Berurutan Dengan Bahasa C++
• Program Potong Rambut Sederhana Menggunakan Bahasa Pemrograman C++ Dan Compiller MinGW

Sebelum memulainya, pada tutorial ini saya menggunakan function sebagai pokok bahasan untuk membuat autonumber ini. Jadi saya sarankan bagi anda yang belum menguasai tentang function untuk mengunjungi artikel saya yang berjudul Function Dan Stored Procedure SQL Server. Artikel tersebut memberikan contoh program dan penjelasan tentang function di SQL Server.

Jika anda sudah memahami tentang function, mari kita lanjut ke tutorial autonumber ini. Simak baik - baik.

1. Membuat Database Dan Tabel Dengan ID Tipe Data Char(12)

Disini kita perlu membuat database dan tabel dengan tipe data char(12) untuk id tabel. Sebelum membuatnya, kita perlu membuat satu tabel dengan id int identity saja. Karena id dari tabel kedua ini digunakan untuk primary key tabel pertama.

Create database Contoh

Kita menggunakan program diatas untuk membuat databas. Dengan begitu, maka settingan database ditentukan secara default. Apabila anda ingin mengubah settingan database, silahkan kunjungi artikel saya tentang cara membuat database dengan SQL Server.

use Contoh

Script diatas digunakan untuk memilih database yang akan kita pilih. Disini saya menggunakan database contoh.

create table jurusan(
id_jurusan int identity primary key not null,
nama_jurusan varchar(50) not null
)

Script diatas adalah script untuk membuat tabel jurusan. Pada tabel ini, kita cukup menjadikan id sebagai integer saja. Hal ini bertujuan untuk kemudahan. Lalu setelah id kita beri kolom nama jurusan.

insert into jurusan(nama_jurusan) 
values('Sistem Informatika'),('Tekhnik Informatika'),('Manajemen Informatika')

Pada script diatas, kita menginputkan data pada tabel jurusan. Disini saya menambahkan tiga buah data. Lihatlah yang saya input adalah nama_jurusan saja. Hal ini disebabkan id pada tabel jurusan sudah di setting identiti atau autoincrement. Dengan begitu id akan terus bertambah secara otomatis.

Lalu kita execute dan lihat hasilnya.

Gambar diatas adalah hasil dari pembuatan dan penginputan data pada tabel jurusan. Kita bisa melihat data - data yang sebelumnya kita coba inputkan telah masuk pada table jurusan. Artinya proses pembuatan database dan insert telah berhasil.

create table mahasiswa(
id_mahasiswa char(12) primary key not null,
nama_mahasiswa varchar(50) not null,
jkel char(1) not null,
alamat varchar(100) not null,
tgl_lahir date not null,
id_jurusan int foreign key references jurusan(id_jurusan) not null
)

Setelah kita selesai dengan tabel jurusan, selanjutnya adalah membuat tabel mahasiswa dimana tabel tersebut berisi id_mahasiswa dengan tipe data char(12). Pada tabel inilah kita menggunakan autonumber dengna format tanggal, kode, dan urutan. Untuk kolom lain, anda bisa menambahnya ataupun mengurangi sesuai dengan selera anda. Karena disini hanya id yang diutamakan.

2. Membuat Function Untuk Autonumber Dengan Kombinasi Tahun, Kode Jurusan, Dan Nomer Urut (2018.01.0001)

Sekarang kita mulai untuk membuat function yang mana function itu berfungsi untuk menyusun autonumber sedemikian rupa hingga memperoleh hasil yang diinginkan. Disini saya membuat function denga format autonumber tahun.kode jurusan.nomer urut (2018.02.0001)

create function auto_tgl_jurusan(@jurusan int)

Program diatas digunakan untuk membuat function. Disini kita buat auto_tgl_jurusan sebagai nama function dengan @jurusan sebagai parameter. Artinya tiap kali kita memanggil function tersebut, kita juga harus melibatkan parameter didalamnya.

returns char(12)
as begin

Nilai yang dikembaikan adalah char(12). Id yang kita buat seluruhnya memiliki jumlah 12 karakter. Jadi disini saya memberikan 12 sebagai nilai kembali. Sebenarnya anda bisa mengurangi ataupun menambahnya. Tapi jika ditambah misal 13, maka satu karakter terakhir tidak akan disimpan di data atau truncated. Jika kurang, maka hasilnya tidak sesuai dengan yang kita inginkan. Karena itu kita buat pas saja.

declare @urut int, @hasil char(12)

Pendeklarasian yang kita buat adalah @urut dengan tipe data integer dan @hasil dengan tipe data char(12). @hasil memiliki tipe data yang sama dengan nilai kembali karena pada akhirnya nanti @hasil inilah yang akan dikembalikan.

SELECT @urut = CONVERT(INT, isnull(max(right(id_mahasiswa,4)),0)) from mahasiswa 

Pada program diatas, kita memasukkan nilai pada variabel @urut. @urut sendiri adalah variabel dengan tipe data integer. Sementara yang pada tabel mahasiswa, id_mahasiswa bertipe data char. Karena itu kita Convert id dari char ke int.

Pada tabel yang baru kita buat, belum ada data sama sekali. Karena itu kita gunakan code isnull. Code tersebut berfungsi untuk menghasilkan angka nol(0) jika data yang dimaksud tidak ada. Dengan begitu maka function bisa dibuat ketika data masih nol dan menghasilkan nilai null. Dengan isnull, maka akan mengganti nilai null dengan 0.

Pada dasarnya, program diatas adalah program untuk menampilkan semua data mahasiswa. Bagaimana jika sudah ada data yang terisi? Padahal yang kita butuhkan adalah data terakhir dari mahasiswa. Karena itu kita gunakan code max. Code tersebut berfungsi untuk mengambil nilai terakhir dari data mahasiswa.

Lalu kita ingat bahwa format dari autonumber tersebut adalah tanggal, kode jurusan, dan angka urut dimana tahun ada di paling depan dilanjutkan dengan kode jurusan dan angka urut (2018.01.0001). Kita lihat terdapat tanda baca titik(.) sebagai pemisah antara tanggal, kode, dan angka urut. Padahal tanda baca tidak bisa di convert ke integer. Karena itulah, kita gunakan code right yang mana code tersebut berfungsi untuk mengambil digit dari kanan. Code dasar dari right adalah right(kolom, jumlah_digit). Pada contoh program diatas, kita mengambil jumlah digit 4. artinya kita mengambil 4 digit dari sebelah kanan (2018.01.0001) menjadi 0001. Namun karena integer tidak mendukung 0 didepan, maka nilai yang didapat adalah 1 atau nilai terakhir setelah nol).

Karena data pada tabel mahasiswa belum ada. maka nilai yang didapat dan disimpan ke variabel @urut adalah 0.

SET @hasil = Convert(char(4),YEAR(getdate()))+'.0'+CONVERT(char(1),@jurusan)+'.'+right('000'+CAST( @urut+1 as VARCHAR(4)), 4)

Program diatas adalah untuk menginput variabel @hasil. Dimana hasil digunakan untuk menyimpan format autonumber yang sudah jadi. Autonumber yang kita gunakan adalah 2018.01.0001. Karena itu kita tambahkan tahun di awal dan diteruskan dengan titik dan kode jurusan.

Pada awal dari script tersebut, kita menemukan deklarasi tahun yang dikonversi ke char(4). Hal ini disebabkan karena date dan char itu berbeda. Karena itu kita mengambil tahun sekarang dan kemudian kita convert ke char(4).

@jurusan adalah parameter dari fungsi yang kita buat. Parameter tersebut ibarat sudah ada isinya dan bernilai integer. Karena itu kita perlu untuk mengubah @jurusan dari integer ke char. Lalu kita tambahkan juga 0 didepannya.

Setelah kita menyimpan @urut sebelumnya, kita tadi sudah mendapatkan nilai tersebut. Namun yang kita butuhkan adalah nilai yang berbeda dengan @urut karena @urut menyimpan nilai yang sudah disimpan di tabel mahasiswa. Maka dari itu kita tambahkan @urut+1. Hal ini untuk menambah jumlah @urut sendiri. Misal @urut bernilai 0. Maka ditambahkan 1 dan menjadi 1. Yang kemudian kita Convert ke varchar(4). Varchar sendiri bersifat fleksibel.

Format dari autonumber yang kita buat adalah 2018.01.0001. Jika kita hanya menggunakan getdate()+@jurusan+@urut+1, maka hasil yang diperoleh adalah 2018.01.1. Karena itu diantaranya kita tambahkan '000'. Hal tersebut dikarenakan @urut bertipe data integer yang mana tidak mendukung angka nol didepan.

Sekarang masalahnya adalah bagaimana jika @urut bernilai lebih dari satu digit? 99 misal. Jika kita hanya menggunakan format getdate()+@jurusan+'000'+@urut+1, maka yang dihasilkan adalah 2018.01.000100. Padahal id pada tabel mahasiswa hanya menyimpan 12 digit saja. Artinya yang tersimpan adalah 2018.01.0001. Itu menyebabkan error dimana autonumber tersebut sebelumnya sudah ada. Karena itu kita membutuhkan code right. dimana code tersebut digunakan untuk mengambil 4 digit terakhir dari data yang dihasilkan. getdate()+@jurusan+right('000'+@urut+1) Begitulah kasaran dari contoh program.

RETURN @hasil
END

Setelah semuanya selesai. Format id sudah dibuat dan sudah dimasukkan ke variabel @hasil. Maka kita hanya tinggal mengembalikan nilai tersebut menggunakan perintah return. Lalu kita gunakan end untuk mengakhiri function. Dengan begitu, function sudah berhasil dibuat.

Hasil keseluruhan function

--tgl.jurusan.0001 (2017.01.0001)
create function auto_tgl_jurusan(@jurusan int)
--Nilai yang dikembalikan
returns char(12)
as begin
--Deklarasi variabel yang dibutuhkan
declare @urut int, @hasil char(12)

--Mengambil 4 digit terakhir dari kolom id pada table mahasiswa
SELECT @urut = CONVERT(INT, isnull(max(right(id_mahasiswa,4)),0)) from mahasiswa 

--Membuat autonumber dengan format A0001 dan dimasukkan ke variabel @hasil
SET @hasil = Convert(char(4),YEAR(getdate()))+'.0'+CONVERT(char(1),@jurusan)+'.'+right('000'+CAST( @urut+1 as VARCHAR(4)), 4)

--Mengembalikkan nilai @hasil
RETURN @hasil
END

Dari berbagai penggalan program diatas yang sudah dijelaskan satu persatu, maka program diatas ini adalah hasil dari keseluruhan program function autonumber yang sudah dijadikan satu. Penggalan program sebelumnya adalah bagian dari function diatas yang mana saya pisahkan dan dijelaskan satu persatu.

3. Menginput Data Dengan Format Autonumber Tahun, Kode Jurusan, Angka Urutan Lalu Menampilkan Data Tersebut. 

Setelah kita mengikuti langka satu dan langkah dua diatas, artinya kita sudah membuat tabel dan function sesuai kebutuhan. Saat ini kita hanya perlu menambahkan data pada tabel mahasiswa dengan id berformat autonumber dari function tersebut.

insert into mahasiswa(id_mahasiswa, nama_mahasiswa, alamat, tgl_lahir, jkel, id_jurusan) values
(dbo.auto_tgl_jurusan(1), 'Fadhlillah Fadhlan', 'Banyubiru', '1994-03-03','L',1),
(dbo.auto_tgl_jurusan(2), 'Choirul Umam', 'Gunung Kidul', '1994-10-25','L',2),
(dbo.auto_tgl_jurusan(3), 'Reza Pratama', 'Balikpapan', '1995-08-14','L',3),
(dbo.auto_tgl_jurusan(2), 'Ikhwan Candra Nugraha', 'Cilacap', '1994-11-14','L',2)

Disini kita inputkan 4 data pada tabel mahasiswa. Pada masing - masing data yang diinput, kita menginput id dengan cara memanggil function dengan cara yang sama, sementara untuk kolom yang lain bisa diisi dengan bebas. Disini saya menginput dengan data diatas. Silahkan kunjungi artikel saya tentag CRUD Pada SQL Server.

select id_mahasiswa, nama_mahasiswa, jkel, alamat, tgl_lahir, m.id_jurusan, nama_jurusan from mahasiswa m 
join jurusan on m.id_jurusan = jurusan.id_jurusan

Program diatas untuk menampilkan data dari tabel mahasiswa dan jurusan. Disini saya menggunakan SELECT JOIN untuk menampilkan data dari kedua tabel tersebut. Silahkan kunjungi artikel saya tentang SELECT, SELECT JOIN, DAN PIVOT SQL SERVER.

Lalu Execute dan lihat hasilnya.

Dari gambar diatas, kita bisa melihat bahwa id menggunakan format tanggal, kode jurusan dan angka urut secara berurutan dan terus bertambah seiring dengan data yang ditambahkan. Jika tampilan sudah seperti diatas. Maka autonumber dengan format tanggal, kode jurusan dan angka urut sudah berhasil dibuat.

Penutup

Cara diatas memang sangat efisien dan membuat tampilan lebih menarik. Namun perlu diketahui bahwa tipe data char adalah tipe data yang berat. Dan tipe data tersebut memakan ram lebih banyak daripada integer yang notabene sangat ringan. Karena itu saya sarankan jika project yang dibuat adalah project yang benar - benar digunakan pada sebuah perusahaan untuk tidak menggunakan cara ini. Cara ini hanya memberat - beratkan ram dan mengakibatkan penyimpanan data tidak maksimal. Namun bila project yang dibuat hanya sekedar tugas akhir atau final project, anda boleh - boleh saja menggunakan cara ini asalkan tidak benar - benar digunakan di perusahaan atau toko saja.

Demikianlah tutorial yang saya berikan. Apabila ada kekurangan saya mohon maaf . Terimakasih telah berkunjung di blog saya yang sederhana ini. Silahkan tinggalkan jejak di kolom komentar. Assalamu'alaikum wr.wb.