Database

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary. (From Wikipedia)

Fungsi/subrutin yang tersimpan dalam database. Di Microsoft SQL Server 2005, kita bisa memanggil semua listing stored procedure dalam database kita dengan menggunakan perintah query berikut:

select *

from information_schema.routines

where routine_type = ‘PROCEDURE’

Perintah di atas akan menampilkan semua informasi mengenai SP yang ada di database. Termasuk tanggal buat, tanggal di ubah dan lain-lain. Tetapi perintah di atas hanya berlaku untuk login sysadmin.

Membuat Stored Procedure.

Query di atas adalah langkah sederhana untuk membuat sebuah stored procedure. Kita bisa membuat sebuah SP tanpa parameter dan dengan parameter. Kata kunci untuk mengingat nya adalah Create…As…Begin. Untuk melakukan modifikasi terhadap SP, gantilah kata kunci CREATE menjadi ALTER. Di bagian BEGIN… END inilah kita menulis sintaks programming logic. Bisa hanya memanggil sebuah query terhadap tabel, view, memanggil sp lainnya, membuat cursor dan lain-lain. Menulis sintaks stored procedure tidak semudah menulis di visual studio sebuah program. Disini tidak ada intellisense maupun debug. Murni dengan berlatih, maka kita akan membuatnya menjadi lebih baik.

Jika tidak hafal perintah creat SP atau takut terjadi kesalahan, maka dari sql server management studio, kita bisa membuat SP dari object explorer. Perhatikan gambar di bawah. Ini adalah sebuah contoh membuat sebuah SP baru dari Object Explorer.

Sample 1, Stored Procedure untuk memanggil tabel.

Untuk sample ini, saya menggunakan database Northwind. Di atas adalah query sederhana untuk membuat sebuah SP. Diharapkan SP ini dapat memanggil semua data dari tabel Products. Eksekusilah perintah query di atas, jika berhasil maka dari Object Explorer akan bertambah sebuah Stored Procedure seperti gambar di bawah:

Lihatlah gambar di atas, telah terbentuk sebuah Stored Procedure dengan nama sp_getproducts. Sampai dengan tahapan ini, dari sisi sintaks query, apa yang kita tulis sudah benar.  Jika tidak benar, maka akan muncul exception dari sql server, misalnya kesalahan deklarasi variabel, query yang salah dan lain-lain.

Selanjutnya adalah memanggil SP tersebut. Gunakan keyword EXEC  diikuti dengan nama Stored Procedure untuk melakukan pemanggilan atau eksekusi terhadap SP yang kita buat. Perhatikan gambar di bawah:

Ketika kita melakukan eksekusi sp_getproducts, maka SP tersebut akan mengembalikan data dari tabel Products. Hal ini cocok dengan logic kita di bagian body dari SP, yaitu SELECT * FROM Products. Jika anda memiliki sebuah View, maka anda dapat memanggil View tersebut dengan mengubah query menjadi SELECT * FROM <NamaView>.

Kita juga bisa memfilter data dengan kata kunci WHERE dengan memodifikasi query menjadi SELECT * FROM Products WHERE CategoryID = 1. Tetapi cara ini sangat statis, tidak dinamis. Sangat tidak baik untuk membuat 1 buah SP untuk 1 buah Category. Jika menginginkan SP yang dinamis dan reusable, gunakanlah parameter pada SP anda.

Sample 2, Stored Procedure dengan Parameter.

Hal yang paling penting disini adalah pendeklarasian variabel pada sql server. Gunakan keyword DECLARE untuk mendeklarasikan variabel, misalnya kita ingin membuat variabel dengan tipe data Int:

DECLARE @angka INT

Berbeda dengan tipe data int, tipe data yang bersifat string seperti char, varchar dll, harus dibuatkan panjang karakternya, misal:

DECLARE @kalimat VARCHAR(20)

Nah, sekarang kita akan melakukan modifikasi terhadap SP yang kita buat sebelumnya. Melakukan modifikasi bisa dari object explorer, klik kanan pada nama SP, kemudian klik Modify.

Selanjutnya lakukan seperti pada gambar:

Setelah nama SP, yaitu sp_getproducts, sisipkan sebuah variabel atau argument @categoryID dengan tipe data INT. Langkah selanjutnya adalah melakukan passing variabel @categoryID tersebut ke dalam body Stored Procedure kita. Jika telah selesai, eksekusi query tersebut, jika tidak ada exception, maka langkah yang kita buat sudah benar.

Selanjutnya adalah cara memanggil stored procedure tersebut. Panggil SP seperti biasa dengan keyword EXEC, kemudian tambahkan argumen di belakang nama SP sesuai tipe data:

EXEC sp_getproducts 1

Perintah di atas akan memfilter products dengan category ID = 1. Jika kita mengganti angka 1 dengan angka lain, maka SP akan memfilter data dari Products sesuai dengan angka Category yang di passing ke argumen.

Biar lebih clear lagi, saya akan ubah sampel kita menjadi lebih kompleks. Misal saya ingin melakukan join terhadap tabel Categories dan kemudian membuat SP yang mempassing nama kategori, jadi bukan ID dari kategori. Perhatikan query di bawah:

ALTER PROCEDURE [dbo].[sp_getproducts]
@categoryName VARCHAR(50)
AS
BEGIN
    SELECT P.ProductName, C.CategoryName, P.UnitPrice  FROM Products P
    INNER JOIN Categories C ON P.CategoryID = C.CategoryID
    WHERE C.CategoryName = @categoryName
END

Untuk memanggilnya lakukan perintah seperti ini:

EXEC sp_getproducts ’Beverages’

Jika ingin menggunakan keyword LIKE, modifikasi SP kita menjadi:

ALTER PROCEDURE [dbo].[sp_getproducts]
@categoryName VARCHAR(50)
AS
BEGIN
    SELECT P.ProductName, C.CategoryName, P.UnitPrice  FROM Products P
    INNER JOIN Categories C ON P.CategoryID = C.CategoryID
    WHERE C.CategoryName LIKE '%'+@categoryName+'%'
END

Menggunakan Temporary Tabel.

Sebenarnya dari beberapa BLOG (yang ane dah lupa blog nya), tidak disarankan untuk menggunakan temp table. Tapi tidak ada salahnya kita mencoba, toh teknologi ini telah disertakan di berbagai macam vendor database. Temp tabel tidak bisa terdeteksi pada object explorer. Temp tabel akan terhapus secara otomatis.

Nama dari temporary tabel biasanya dimulai dengan tanda ‘#’. Berikut adalah sintaks untuk membuat temp table:

    CREATE TABLE #tabelku     
     ( id int,         
       Kolom1 varchar(20),   
       Kolom2 varchar(20),
       Kolom3 datetime,        
       Kolom4 char(1))

Oke, langsung saja kita ke sampel, ceritanya saya akan mengurutkan data pada tabel products. Pengurutan data saya lakukan dengan cara mengelompokkan berdasarkan CategoryID. Jika CategoryID berubah, maka nomor urut akan di reset kembali menjadi 1.

Ada banyak pendekatan untuk menyelesaikan masalah ini. Pilihan saya adalah menggunakan temp tabel. Saya akan melakukan query dengan filter berdasarkan CategoryID, kemudian saya akan memasukkan ke dalam temp tabel dengan menggunakan keyword SELECT * INTO. Sekedar catatan, jika menggunakan SELECT * INTO, kita tidak perlu membuat temp tabel dengan keyword CREATE lagi.

Sintax SQL:

CREATE PROCEDURE sp_laporan
@categoryid INT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ProductName, UnitPrice INTO #laporan FROM Products
      WHERE CategoryID = @categoryid
      ORDER BY ProductName ASC
      ALTER TABLE #laporan
      ADD no_urut INT IDENTITY(1,1)
      SELECT no_urut, ProductName, UnitPrice FROM #laporan
END

Jalan cerita dari sintaks di atas:

• membuat SP dengan nama sp_laporan
• mempassing parameter @categoryid, parameter ini nanti dibutuhkan untuk filter data pada SELECT query.
• Query dengan keyword SELECT … INTO, disini kita memasukkan data langsung ke dalam temp tabel #laporan. Pada case ini belum dibutuhkan looping terhadap data.
• Kita lakukan ALTER terhadap tabel #laporan, menambahkan field no_urut dengan tipe data INT dan bersifat IDENTITY, sehingga menghasilkan auto number.
• Ketika saya melakukan pemanggilan terhadap SP, maka akan dihasilkan bentuk data seperti gambar di bawah.

Pengurutan data bisa terjadi juga pada field UnitPrice. Jika dikehendaki pengurutan data dari UnitPrice dari harga paling murah ke harga paling mahal dan menambahkan nomor urut, maka modifikasi saja SP tersebut, jika sebelumnya pada keyword ORDER BY kita urutkan berdasarkan ProductName, maka gantilah ProductName menjadi UnitPrice. Maka akan menghasilkan seperti gambar di bawah ini:

Menggabungkan hasil dari beberapa SP

Kita bisa juga menggabungkan hasil dari beberapa SP kedalam sebuah temp tabel. Masih dengan contoh sebelumnya dimana kita concern terhadap no_urut. Coba perhatikan query di bawah ini:

CREATE PROCEDURE sp_laporanglobal
AS
BEGIN
      SET NOCOUNT ON;
      CREATE TABLE #laporanglobal
      (
            Nmr INT IDENTITY(1,1),
            no_urut INT,
            ProductName VARCHAR(50),
            UnitPrice MONEY
      )
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 1
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 2
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 3
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 4
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 5
      SELECT * FROM #laporanglobal
      DROP TABLE #laporanglobal
END
GO

Jalan ceritanya:

• Saya membuat temp tabel bernama #laporanglobal, dengan 4 buah field. Field Nmr adalah identity, sementara field lainnya adalah field yang kita ambil dari Stored Procedure sp_laporan.
• Selanjutnya saya lakukan INSERT ke dalam temp tabel, sambil melakukan eksekusi terhadap SP. Teknik yang cukup akrobatik sebenarnya hehehe. Untuk sementara saya kasi contoh yang seperti ini, karena kita belum membicarakan looping.

Sepertinya pembicaraan kita mengenai dasar-dasar SP, cukup sampai disini. Mungkin ke depan kita bicarakan masalah CURSOR dan LOOPING. Apa yang anda pelajari sampai di topik ini saya rasa sudah cukup banyak. Jika masih belum puas, saya akan berikan beberapa keyword untuk googling:

• JOIN multiple Stored Procedure
• Temp Table on Stored Procedure
• ALTER and CREATE Table.

Percayalah, saya hanya googling keyword di atas untuk membuat artikel ini.