Store Procedure

Store Procedure adalah kumpulan Pre-defined Transact-SQL yang digunakan untuk melakukan tugas/task khusus. Dalam Store Procedure dapat berisi beberapa statement dan setiap statement di kelompokan untuk satu object database.

Store Procedure terdiri dari:
1. Statemen CREATE PROC {PROCEDURE}
2. Nama Procedure;
3. Parameter list
4. SQL statement.

Banyak option lainnya dalam mendefinisikan store procedure, dalam tulisan ini hanya beberapa saja yang disebutkan, hanya sebagai gambaran awal dalam membuat store procedure.

Penggunaan store procedure dalam sebuah pemrograman database memiliki beberapa keuntungan atau kelebihan sebagai berikut:
1. Performance
2. Security
3. Modifications/Maintenance
4. Minimal processing at the client.
5. Network traffic

CREATE PROCEDURE SP_LihatBarangA
AS
BEGIN
select * FROM dbo.Barang
END

CREATE PROCEDURE SP_LihatBarangAwalaan
<@awalan varchar(20)
AS
BEGIN
select * FROM dbo.Barang
where NamaBarang like @awalan + '%'
END

CREATE PROCEDURE SP_LihatBarangKataTertentu
@kata_tertentu varchar(50)
AS
BEGIN
select * FROM dbo.Barang
where NamaBarang like @kata_tertentu + '%'
END

exec SP_LihatBarangKataTertentu 'as'

soal1

@x=jumlah barang dgn harga beli < 100000
@y=jumlah barang dgn harga beli 100000-500000
@z=jumlah barang dgn harga beli > 500000

CREATE PROCEDURE SP_Penjualan
@tahun int
AS
BEGIN

declare @jan_sd_mar decimal(18,2)
declare @apr_sd_jun decimal(18,2)
declare @jul_sd_sep decimal(18,2)
declare @okt_sd_des decimal(18,2)

select @jan_sd_mar = SUM (TotalFaktur) FROM dbo.FJ
where TglFJ between '01/01/' + convert(varchar(4),@tahun) and '03/31/' + convert(varchar(4),@tahun)+' 23:59:59'
select @apr_sd_jun = SUM (TotalFaktur) FROM dbo.FJ
where TglFJ between '04/01/' + convert(varchar(4),@tahun) and '06/30/' + convert(varchar(4),@tahun) +' 23:59:59'
select @jul_sd_sep = SUM (TotalFaktur) FROM dbo.FJ
where TglFJ between '07/01/' + convert(varchar(4),@tahun) and '09/30/' + convert(varchar(4),@tahun)+' 23:59:59'
select @okt_sd_des = SUM(TotalFaktur) FROM dbo.FJ
where TglFJ between '10/01/' + convert(varchar(4),@tahun) and '12/31/' + convert(varchar(4),@tahun)+' 23:59:59'

select @jan_sd_mar,@apr_sd_jun,@jul_sd_sep,@okt_sd_des
END

exec SP_Penjualan 2005

SPJumlahJuni2005 dalam satu storeprocedure(soal 3)
alter PROCEDURE SP_PenjualanJuni
AS
BEGIN
declare @a int
declare @b int
declare @x int
declare @y money

select @a = COUNT(DISTINCT KodePelanggan) FROM dbo.FJ
where TglFJ between '06/01/2005' and '06/30/2005 23:59:59'

select DISTINCT @b = COUNT(DISTINCT b.NamaBarang)FROM dbo.FJDet fd join FJ f on (fd.NoFJ = f.NoFJ)join Barang b on(fd.KodeBarang = b.KodeBarang)
where TglFJ between '06/01/2005' and '06/30/2005 23:59:59'

select @x = SUM(fd.Qty)FROM FJDet fd join FJ f on (fd.NoFJ = f.NoFJ)
where TglFJ between '06/01/2005' and '06/30/2005 23:59:59'

select @y = SUM(TotalFaktur)FROM FJ
where TglFJ between '06/01/2005' and '06/30/2005 23:59:59'

select @a ,@b ,@x ,@y
END

exec SP_PenjualanJuni

Studi kasus :
Berikut pola penomoran reset per tahun, setiap gudang punya nomor urut sendiri (Gudang G1 dan G2)

TbPOKasus1
NomorPO TGLPO GUDANG KODESUPPLIER
PO-2008-G1-00001 16/09/2008 G1 S-00001
PO-2008-G1-00002 16/09/2008 G1 S-00001
PO-2008-G2-00001 17/09/2008 G2 S-00002
PO-2008-G2-00002 17/10/2008 G2 S-00002
PO-2009-G1-00001 17/10/2009 G1 S-00002
PO-2009-G2-00001 17/10/2009 G2 S-00002

alter PROCEDURE SP_POKasus1
@TglPO datetime,@Gudang char(2) ,@KodeSupplier nvarchar(50)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(50)


select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from dbo.TbPOKasus1
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)) AND
substring(NomorPO,9,2) = @Gudang


if @NOAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yy, @TglPO )) + '-' + @Gudang + '-' +
RIGHT('00000'+Convert(varchar(20), @NoBaru), 5)


Insert into tbPOKasus1 (NomorPO, TglPO, Gudang,KodeSupplier)
Values (@NomorPO, @TglPO,@Gudang ,@KodeSupplier)
END

exec dbo.SP_POKasus1 '09/16/2009','G1','S-00001'
--delete from dbo.tbPOKasus1
select * from dbo.TbPOKasus1


create PROCEDURE SP_POKasus2
@NamaBarang nvarchar(50) ,@Stok int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @KodeBarang varchar(50)



select @NoAkhir = convert(int,max(right(KodeBarang,3)))
from dbo.TbPOKasus2
where left(@NamaBarang,1) = left(KodeBarang,1)


if @NOAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @KodeBarang = left(@NamaBarang,1)+ RIGHT('000'+Convert(varchar(20), @NoBaru), 4)


Insert into tbPOKasus2 (NamaBarang, KodeBarang,Stok)
Values (@NamaBarang, @KodeBarang ,@Stok)
END

exec dbo.SP_POKasus2 'Meja Biro','5'

select * from dbo.TbPOKasus2

Buat SPInsertPelanggan, Update dan Delete
CREATE PROCEDURE SP_InsertFJ
@NoFJ nvarchar(12),
@KodePelanggan nvarchar(12),
@TotalFaktur money
AS
BEGIN

if exists (SELECT KodePelanggan
from dbo.Pelanggan where KodePelanggan = @KodePelanggan)
begin

if exists (SELECT NoFJ
from dbo.FJ where NoFJ = @NoFJ)

begin
select 'data sudah ditemukan, proses insert gagal'
end

else

begin
INSERT INTO [SIPP].[dbo].[FJ]
(NoFJ
,KodePelanggan
,TotalFaktur)

VALUES
(@NoFJ
,@KodePelanggan
,@TotalFaktur)

UPDATE [SIPP].[dbo].[Pelanggan]
SET [KodePelanggan] = @kodepelanggan
,[Piutang] = piutang+@TotalFaktur
WHERE KodePelanggan = @KodePelanggan

select 'data berhasil terisi'
end
end

else

begin

select 'kode pelanggan tidak terdaftar'

end

END
GO

EXEC SP_InsertFJ

use SIPP
CREATE PROCEDURE SP_DeletePelanggan
@pKodePelanggan nvarchar(10)
AS
BEGIN
IF EXISTS (SELECT KodePelanggan FROM Pelanggan
where KodePelanggan = @pKodePelanggan)
BEGIN
DELETE from Pelanggan

WHERE KodePelanggan = @pKodePelanggan
SELECT 'Delete berhasil'
END

ELSE

BEGIN
SELECT 'Data pelanggan TIDAK ada,delete gagal!'
END
END
use SIPP
exec SP_DeletePelanggan 'C-00001'

alter PROCEDURE SP_UpdatePelanggan
@pKodePelanggan nvarchar(10),
@pNamaPelanggan nvarchar(30),
@pPiutang money

AS
BEGIN
IF EXISTS (SELECT KodePelanggan FROM Pelanggan
where KodePelanggan = @pKodePelanggan)
BEGIN
UPDATE Pelanggan

SET

NamaPelanggan = @pNamaPelanggan,
Piutang = @pPiutang

WHERE

KodePelanggan = @pKodePelanggan
SELECT 'Update berhasil'
END

ELSE

BEGIN
SELECT 'Data pelanggan TIDAK ada,update gagal!'
END
END


exec SP_UpdatePelanggan 'C-00001','Kartika Motor',1