Stored Procedure di Database

Kelebihan query di stored procedure dibandingkan dengan query yang diletakkan di aplikasi tier adalah

• membuat proses query SQL menjadi terpusat di data tier

• mengurangi kepadatan trafik jaringan untuk proses query, terutama bila query itu dilakukan berulang-ulang

• menjadikan kode query menjadi bersifat reusability atau bisa dipakai ulang untuk proses yang lain tanpa membuat query yang sama lagi

• dapat melakukan perubahan di data tier tanpa mengubah kode di sisi aplikasi tier

• memiliki stabilitas waktu respon untuk tiap query-nya

Di Borland Delphi, stored procedure digunakan dengan perantara TADOStoredProc dengan database yang dipakai sesuai dengan property ‘ConnectionString’ yang diset pada komponen ini dan kemudian nama stored procedure (property ‘ProcedureName’) serta parameter-parameternya (property ‘Parameters’) dapat langsung diakses dari sisi client

Berikut adalah contoh stored procedure dengan T-SQL di MSSQL Server untuk proses insert, update dan delete data table :

create procedure retrieveData
@select varchar(1),
@id int = null,
@cat_id int = null,
@dateposted datetime = null,
@subject varchar(100) = null,
@body varchar(max) = null
as
if @select='I'
begin
insert entries (cat_id,dateposted,subject,body)
values (@cat_id,getdate(),@subject,@body)
if @@ERROR 0 goto err_handle
return 0
end

if @select='U'
begin
update entries set cat_id=@cat_id, dateposted=@dateposted, subject=@subject, body=@body
where id=@id
if @@ERROR 0 goto err_handle
return 0
end

if @select='D'
begin
delete entries where id=@id
if @@ERROR 0 goto err_handle
return 0
end

err_handle:
begin
rollback transaction
return -101
end

go

Contoh berikut untuk meretrieve data penjualan dengan melakukan penyaringan data (fungsi ‘case’) pada query-nya

create procedure Retrieve_Penjualan
as
select substring (judul, 1, 20) as Judul,
case jenis
when 'Pemrograman Komputer' then ' Buku Komputer'
when 'Jaringan Komputer' then ' Buku Komputer'
when 'Masakan Modern' then ' Buku Memasak'
else ' Buku Lain'
end as Type,
'Penjualan : ' +
case
when penjualan < 10 then 'kurang'
when penjualan between 10 and 20 then 'sedang'
when penjualan > 20 then 'banyak'
else 'Unknown'
end as Penjualan
from buku
go