Membuat Storeprocedure Select, Insert, Delete, dan Update pada Microsoft SQL Server 2005

Store procedure adalah kumpulan Pre-defined Transact-SQL yang digunakan untuk melakukan tugas atau task khusus. Dalam store procedure dapat berisi beberapa statement dan setiap statement dikelompokan untuk satu object database.

Untuk membuat store procedure, kita hanya menjalankan perintah create procedure diikuti dengan SQL script. Kita bisa membuat store procedure melalui Query Analyzer pada MS SQL Server, atau dengan menggunakan menu New Procedure pada Enterprise Manager.

Store Procedure terdiri dari :

1. Statement CREATE PROC {PROCEDURE}

2. Nama Procedure

3. Parameter list

4. SQL statement.

Penggunaan store procedure dalam sebuah pemrograman database memiliki beberapa keuntungan atau kelebihan sebagai berikut:

1. Performance

Semua perintah SQL, yang kita kirimkan ke database server melalui kumpulan action yang disebut dengan execution. Hal ini menjadikan perintah SQL yang kita kirimkan melalui beberapa proses sebelum data dikembalikan ke client.

User mengirimkan request untuk mengeksekusi store procedure. SQL Server kemudian mengecek apakah ada syntax error. Mengidentifikasi dan melakukan pengecekan alias pada form clausa. Membuat query plan. Meng-compile dan kemudian mengeksekusi query plan untuk kemudian mengembalikan data yang diminta.

Pada versi terbaru SQL server, ada banyak perubahan pemrosesan statement. Store procedure disimpan dalam procedure cache ketika dipanggil, membuat subsequent lebih cepat dipanggil.

2. Security

Store Procedure memberikan keuntungan yang baik dalam hal security. Dengan menggunakan store procedure, kita bisa memberikan permission untuk user yang ditunjuk untuk dapat mengakses data, menekan immense coding yang perlu kita lakukan pada Application Client.

3. Modifications/Maintenance

Jika kita menggunakan store procedure untuk mengakses database, setiap perubahan pada database dapat dipantau berdasarkan client application. Hal ini dimungkinkan karena kita dapat tahu persis dimana data diakses, dan kita juga tahu dimana kita harus melakukan perubahan. Hal ini berarti kita tidak perlu pusing kepala dengan ribuan baris source code pada client application untuk menemukan baris mana yang perlu dirubah.

4. Minimal Proccessing at Client

Ketika membuat aplikasi client/server, normalnya adalah client yang bertanggung jawab mengenai integritas data yang masuk ke dalam database. Mengelola Primary Key, Foreign Key, semuanya dilakukan oleh client, dan database server hanya bertugas menyimpan data yang diberikan client.

Dengan menggunakan store proocedure membantu kita untuk membuat batch perintah SQL, yang bisa digunakan untuk mengatur transaction, constraints dan lain-lain. Hanya sedikit daya yang ditulis pada client apllication, menjadikan aplikasi client menjadi lebih ringan. Aplikasi ini akan lebih terfokus pada menampilkan data untuk keperluan user dan aplikasi client tidak tahu banyak mengenai database.

Misalnya, jika database yang berisi ribuan rows dan ratusan table diperlukan beberapa perhitungan sebelum melakukan update pada setiap record. Jika mengambil data secara lengkap ke client, dan meminta client computer untuk memproses data secara lengkap, bisa dibayangkan apa yang ditimbulkan. Tetapi jika client bisa mengeksekusi store procedure, ketika selesai melakukan perhitungan untuk melakukan update pada record.Client tidak perlu melakukan proses perhitungan. Hal ini juga menekan proses yang terjadi di client, dan server yang menjalankan kalkulasinya.

5. Network Traffic

Aplikasi pada client selalu me request/ mengirimkan data ke database server. Data ini dikirimkan sebagai packet dan dikirimkan ke jaringan (Network) ke server.

Mari coba kita buat store procedure dari tabel barang yang terdapat pada database produk yang sudah dibuat sebelumnya. Langkah-langkahnya adalah sebagai berikut :

1. Store Procedure dapat ditemukan di produk -> Programmability -> Stored Procedures. Untuk membuat store procedure baru dilakukan dengan klik kanan pada Stored Procedures, kemudian klik New Store Procedure. Dapat dilihat pada gambar di bawah ini :

2. Untuk membuat store procedure select, maka ketikkan

CREATE PROCEDURE stpBarangSelectAll

AS SELECT * FROM barang

3. Kemudian klik Execute atau tekan F5. Jika berhasil maka akan keluar pesan

Command(s) completed successfully.

4. Refresh store procedure, maka store procedure dengan nama stpBarangSelectAll akan muncul.

5. Ulangi langkah 1 sampai 4 untuk membuat store procedure yang lain kecuali perintah pada langkah 2 dapat diisi dengan :

Untuk Insert dapat diketikkan syntax :

CREATE PROCEDURE stpBarangInsert @id_barang varchar(5), @nama varchar(50), @stok int, @ket varchar(5) AS INSERT INTO barang VALUES(@id_barang,@nama,@stok,@ket)

Untuk Delete dapat diketikkan syntax :

CREATE PROCEDURE stpBarangDelete @id_barang varchar(5) AS DELETE FROM barang where id_barang=@id_barang

Untuk Update dapat diketikkan syntax :

CREATE PROCEDURE stpBarangUpdate @id_barang varchar(5), @nama varchar(50), @stok int, @ket varchar(5) AS UPDATE barang SET nama=@nama,stok=@stok,ket=@ket WHERE id_barang=@id_barang

6. Setelah dibuat semua store procedure jangan lupa untuk refresh store procedure agar store procedure yang kita buat muncul. Hasilnya dapat dilihat pada gambar berikut.

Selamat mencoba. Moga success.

Selanjutnya kita akan coba bikin Web Service dari store procedure yang udah kita buat, berikut cara koneksi melalui web service yang diletakkan pada web config. Insya Allah....