SQL Server - Cara Membuat Stored Procedure Untuk CRUD Database

March 01, 2018 1 comment

Assalamu'alaikum wr.wb.
Pada kesempatan ini, saya akan memberikan tutorial tentang stored procedure pada SQL Server. Tutorial ini sebelumnya sudah saya buat pada artikel yang sebelumnya Stored Procedure Dan Funciton Pada SQL Server. Namun setelah saya lihat baik - baik, ternyata artikel tersebut masih memiliki beberapa kekurangan salah satunya adalah pembahasan tentang stored procedure yang kurang detail. Hal tersebut mengakibatkan kurangnya kualitas artikel untuk membuat pembaca lebih paham tentang stored procedure ini.

Saat ini banyak sekali developer yang mengembangkan aplikasi yang berbasis database dengan menggunakan stored procedure dikarenakan jika kita menggunakan stored procedure, maka kita bisa memisahkan proses CRUD database dan program lain sehingga akan lebih mudah untuk dianalisa ketika terjadi kesalahan dan lebih mudah dalam pengembangan program. Dengan begitu maka pembuatan aplikasi menjadi lebih efisien.

Cara membuat stored procedure di SQL Server memiliki banyak cara. Seiring dengan perkembangan dari SQL Server ini membuat aplikasi tersebut menjadil lebih ramah untuk dipelajari. Namun yang akan saya bahas kali ini adalah cara untuk membuat stored procedure menggunakan coding SQL karena cara tersebut masih tergolong mudah. Dan dengan cara membuat procedure menggunakan sintax SQL ini, tentu anda dapat memahami tentang stored procedure lebih baik apabila anda sudah memahaminya.

Baca Juga

Fungsi store procedure dalam SQL Server sebenarnya tidak hanya untuk CRUD saja. Sama halnya dengan fungsi pada program seperti C# ataupun Java. Stored procedure bisa dianggap sebagai fungsi void atau fungsi yang tidak mengembalikan nilai. Namun kebanyakan programmer membuat stored procedure untuk CRUD atau menampilkan data yang menggunakan parameter saja karena dengan menggunakan stored procedure, tentu itu sangat mempermudah developer untuk mengembangkan program agar menjadi lebih baik.

1. Pengertian Stored Procedure

Stored procedure adalah susunan kode SQL yang disimpan dalam katalog dalam database dan dapat dipanggil oleh program, trigger, atau bahkan stored procedure. Stored procedure yang memanggil dirinya sendiri disebut rekursif stored procedure. Artinya kita bisa membuat susunan kode SQL yang kita gabungkan pada stored procedure yang nantinya dapat dipanggil menggunakan perintah EXEC.

Oke langsung saja. Berikut ini adalah contoh store procedure sql server yang saya buat menggunakan SQL Server 2014. Mungkin ada beberapa syntax yang tidak didukung oleh SQL Server versi lain, namun secara garis besar susunan dari stored procedure adalah sebagai berikut :

2. Membuat Tabel Untuk Yang Akan Diinput Menggunakan Stored Procedure

Pada langkah ini, kita diharuskan untuk membuat tabel yang mana kita akan menginputkan data pada tabel tersebut menggunakan stored procedure. Dalam pembuatan tabel ini tidaklah berbeda dengan membuat tabel biasa karena memang yang kita buat hanyalah tabel yang biasa digunakan saja.

CREATE TABLE dosen(
 id_dosen int IDENTITY PRIMARY KEY NOT NULL,
 nama_dosen varchar(50) NOT NULL,
 alamat varchar(100) NOT NULL,
 tgl_lahir date NOT NULL,
 jkel char(1) NOT NULL
)

Pada contoh program diatas, kita membuat sebuah tabel dengan nama dosen yang berisikan kolom kolom tertentu seperti id_dosen, nama, alamat, tgl_lahir, dan jkel. Kolom - kolom yang ada pada tabel dosen tersebut memiliki tipe data yang bervariasi tergantung data yang akan kita simpan pada tabel tersebut.

3. Membuat Stored Procedure Untuk CRUD Pada Tabel Yang Telah Dibuat

Setelah kita membuat tabel, maka saat ini adalah membuat stored procedure. Disinilah inti dari permasalahan yang akan kita bahas saat ini. Dengan stored procedure ini, kita tentu hanya perlu memanggil stored procedure tanpa perlu membuat query untuk CRUD berkali - kali. Karena itu, hal ini sangat berguna unuk developer yang bertugas untuk membuat aplikasi yang mungkin nantinya akan membutuhkan perkembangan dari aplikasi tersebut.

Sintaks untuk membuat procedure adalah sebagai berikut :

CREATE PROCEDURE nama_procedure
(@parameter1 tipe_data, @parameter2 tipe_data, @parameter_n tipe_data)
AS BEGIN TRANSACTION
//letakkan Query disini

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

Program diatas adalah susunan untuk membuat stored procedure, dimana terdapat baris awal yaitu nama_procedure, kemudian diisi oleh parameter - parameter yang dibutuhkan. Parameter yang dimasukkan ini harus sesuai dengan kolom dari tabel tersebut. Kemudian kita meletakkan query di tempat yang sudah diberi tanda. Lalu untuk selanjutnya adalah sintax untuk menyeleksi apakah query tersebut error atau tidak. Selanjutnya saya akan memberikan contoh stored procedure untuk CRUD database. Pada contoh tersebut saya membuatnya dengan menambahkan perintah CRUD Database. Apabila anda belum memahami tentang proses CRUD, silahkan kunjungi artikel saya tentang CRUD SQL Server.

Stored Procedure Untuk Insert Tabel

Stored procedure untuk insert atau menambahkan data pada tabel memiliki peranan untuk menginsert data dengan perintah insert into yang mana perintah tersebut sudah disimpan didalam stored procedure sehingga kita hanya perlu memberikan value pada parameter yang disediakan saja.

CREATE PROCEDURE tambah_dosen
(@nama VARCHAR(50), @alamat VARCHAR(100), @tgl_lahir DATE, @jkel CHAR(1))
AS BEGIN TRANSACTION
INSERT INTO dosen (nama_dosen, alamat, tgl_lahir, jkel) VALUES
(@nama, @alamat, @tgl_lahir, @jkel)

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

Pada proses insert, kita memberikan parameter sesuai dengan kolom yang ada pada tabel. Kecuali id karena id merupakan auto_increment. Sedangkan yang lain merupakan value biasa yang memang harus kita isi manual.

Stored Procedure Untuk Update Atau Ubah tabel

Stored procedure untuk mengupdate atau mengubah data dari tabel memiliki peranan untuk menyimpan perintah update ke dalam stored procedure yang kemudian kita isi data tersebut dengan parameter - parameter yang sudah kita berikan value tersebut. Pada dasarnya untuk pemanggilan sama seperti stored procedure untuk insert.

CREATE PROCEDURE ubah_dosen
(@id_dosen int, @nama VARCHAR(50), @alamat VARCHAR(100), @tgl_lahir DATE, @jkel CHAR(1))
AS BEGIN TRANSACTION
UPDATE dosen SET nama_dosen = @nama, alamat = @alamat, tgl_lahir = @tgl_lahir, jkel = @jkel 
WHERE id_dosen = @id_dosen

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

Disini kita buat dengan nama ubah_dosen. Lalu kita membutuhkan parameter - parameter yang ada. Untuk mengubah data. Tentu kita membutuhkan id sebagai penanda bahwa id tersebutlah yang datanya diubah. Sedangkan variabel lain, kita buat dimana data tersebut yang akan diubah berdasarkan id tersebut.

Stored Procedure Untuk Delete Atau Menghapus Data Tabel

Stored procedure untuk delete atau menghapus data dari tabel adalah stored procedure yang menyimpan perintah untuk menghapus data menggunakan syntax SQL yang disimpan didalam stored procedure sehingga kita hanya perlu mengeksekusi stored procedure tersebut jika kita ingin menghapus data dari tabel.

CREATE PROCEDURE hapus_dosen
(@id_dosen int)
AS BEGIN TRANSACTION
DELETE dosen  
WHERE id_dosen = @id_dosen

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

Untuk stored procedure yang digunakan menghapus data, kita hanya perlu parameter id saja karena untuk mennghapus pada umumnya kita hanya memerlukan id. Kita lihat saja querynya dimana query tersebut hanya membutuhkan id sebagai value.

Syntax Untuk Memanggil Stored Procedure

Setelah kita memahami tentang stored procedure, sekarang saatnya untuk mengeksekusi stored procedure yang sudah kita buat tersebut. Untuk memanggil stored procedure membutuhkan Syntax Exec nama_procedure parameter_n. Disini saya contohkan memanggil stored procedure untuk menambah data dengan nama tambah_dosen.

EXEC tambah_dosen 'Hartono Lapan Suroto','Pacitan','1991-08-05','L'

Lihatlah program diatas, Pertama terdapat perintah EXEC yang mana perintah tersebut merupakan perintah untuk mengeksekusi Stored procedure, sedangkan tambah_dosen adalah nama dari stored procedure yang kita panggil. Lalu terdapat tulisan di tanda petik yang mana itu merupakan value dari parameter - parameter yang kita buat sebelumnya.

Menampilkan Data Dari Tabel Dosen

Setelah kita mengeksekusi procedure tambah_dosen. Sekarang kita coba menampilkan data dengan perintah SELECT. Perintah SELECT tersebut merupakkan perintah biasa yang digunakan untuk menampilkan data dari sebuah tabel saja.

SELECT * FROM dosen

Perintah diatas adalah perintah untuk menampilkan data dari suatu tabel. Tentu saja tidak ada bedanya dengan menampilkan data dari tabel tanpa stored procedure karena data yang disimpan menggunakan stored procedure dan yang tidak itu sama saja. Yang berbeda hanyalah prosesnya saja.

Gambar diatas adalah hasil dari proses INSERT menggunakan stored procedure. Jika stored procedure yang anda buat berhasil, maka akan muncul seperti gambar diatas. Sebenarnya cara ini sangatlah mudah karena cara ini merupakan cara yang menjadi sebuah jalur alternatif dimana kita tidak perlu mengetikkan program berulang - ulang.

Penutup

Setelah kita memahami tentang tutorial diatas, kita bisa mengetahui bahwa stored procedure sangat berguna untuk memanipulasi data dari database. Namun sebenarnya stored procedure sendiri tidaklah hanya berguna untuk CRUD saja. Stored procedure juga bisa untuk menampilkan data dan lain - lain yang berhubungan dengan database.

Saat ini memang stored procedure untuk CRUD sudah tidak lagi digunakan karena proses CRUD sudah bisa diatasi dengan menggunakan Framework - framework dari suatu bahasa pemrograman. Seperti yang saya contohkan pada C# Entity framework. Jika kita menggunakan framework tersebut, kita tidak perlu mengetikkan proses CRUD karena proses CRUD tersebut sudah dibuat oleh framework secara otomatis.

Demikian tutorial dari saya. Apabila ada kekurangan saya mohon maaf. Semoga artikel ini menambah wawasan pembaca sekalian. Silahkan tinggalkan jejak di kolom komentar. Assalamu'alaikum wr.wb

Tutor-All Programming