Teknik mengembalikan nilai dengan menggunakan Store Procedure
By: pieter_edison@yahoo.com

Dalam topik ini, kita akan membahas beberapa teknik untuk mengembalikan nilai seperti fungsi didalam SQL Server dapat juga dilakukan dengan menggunakan store procedure.

Jadi ketika anda telah terlanjur menggunakan store procedure dalam “role-bussiness” anda, kemudian ingin mengembalikan nilainya ( biasanya untuk menampilkan nilai field dalam suatu report ), saya akan mencoba menerangkan 3 teknik yang mungkin dapat digunakan.

Teknik yang digunakan bisa dengan cara :
1. Menggunakan OUTPUT variable syntax
2. Menggunakan Temporary Tables
3. Menggunakan statement RETURN

Teknik Pertama – Menggunakan OUTPUT variable

Maksud teknik ini kita dapat mendeklarasikan keword OUTPUT pada variable di store procedure yang telah kita buat untuk mengembalikan nilai keluaran.

Contoh penggunaan secara real menggunakan TSQL.

Misalkan di database anda kita menggunakan database pubs yang telah ada terdapat dalam SQL Server anda. Kita ingin mengembalikan nilai berapa banyak Wilayah/Propinsi “CA” yang ada ( dalam hal ini field state ) didalam table authors.

 Potongan scriptnya

USE PUBS
GO
CREATE PROC spCountState (
@strstate varchar(2),
@intCount int OUTPUT
)
AS
SELECT @intCount = COUNT(*) FROM dbo.authors
WHERE state=@strstate
GO

Nah, setelah anda menjalankan dan mengeksekusi store procedure diatas, kemudian kita akan melihat pengembalian nilai banyaknya state yang mengandung kata “CA”


 Potongan scriptnya





USE PUBS
GO
DECLARE @Count int
EXEC spCountState 'CA', @Count OUTPUT
SELECT TotalState=@Count
GO

Akan menghasilkan pengembalian nilai sebagai berikut :
TotalState
15

Teknik Kedua – Menggunakan Temporary Variabel

Maksud teknik ini kita dapat mengembalikan nilai keluaran menggunakan temporary table di store procedure yang telah kita buat.Temporary table dalam SQL Server bisa dilambangkan dengan # .

Contoh penggunaan secara real menggunakan TSQL.

Sama seperti kasus diatas, saya coba paste kembali kasusnya seperti dibawah ini

Misalkan di database anda kita menggunakan database pubs yang telah ada terdapat dalam SQL Server anda. Kita ingin mengembalikan nilai berapa banyak Wilayah/Propinsi “CA” yang ada ( dalam hal ini field state ) didalam table authors.

NB : Dalam hal ini, untuk mengembalikan nilai ke dalam temporary table kita memodifikasi sedikit store procedure diatas dengan kode script sql dibawah ini.

 Potongan scriptnya

USE PUBS
GO
CREATE PROC spCountState2 (
@strstate varchar(2)
)
AS
SELECT COUNT(*) FROM dbo.authors
WHERE state=@strstate
GO

Nah, setelah anda menjalankan dan mengeksekusi store procedure diatas, kemudian kita akan melihat pengembalian nilai banyaknya state yang mengandung kata “CA”


 Potongan scriptnya




USE PUBS
GO
CREATE TABLE #CountState (
intCount int
)
INSERT #CountState
EXEC spCountState2 'CA'
SELECT * FROM #CountState
GO
DROP TABLE # CountState

Akan menghasilkan pengembalian nilai yang sama seperti teknik 1 diatas sebagai berikut:
TotalState
15

Teknik Ketiga – Menggunakan statement RETURN

Maksud teknik ini kita dapat mengembalikan nilai keluaran menggunakan statement RETURN di store procedure yang telah kita buat.Tapi kelemahannya, statement RETURN ini akan menghasilkan pengembalian nilai keluaran numerik tunggal, tidak bisa beberapa nilai.

Contoh penggunaan secara real menggunakan TSQL.

Sama seperti kasus diatas, saya coba paste kembali kasusnya seperti dibawah ini

Misalkan di database anda kita menggunakan database pubs yang telah ada terdapat dalam SQL Server anda. Kita ingin mengembalikan nilai berapa banyak Wilayah/Propinsi “CA” yang ada ( dalam hal ini field state ) didalam table authors.

NB : Dalam hal ini, untuk mengembalikan nilai ke dalam statement RETURN kita memodifikasi sedikit store procedure diatas dengan kode script sql dibawah ini.

 Potongan scriptnya

USE PUBS
GO
CREATE PROC spCountState3 (
@strstate varchar(2)
)
AS
RETURN (SELECT COUNT(*) FROM dbo.authors
WHERE state=@strstate)
GO

Nah, setelah anda menjalankan dan mengeksekusi store procedure diatas, kemudian kita akan melihat pengembalian nilai banyaknya state yang mengandung kata “CA”



 Potongan scriptnya

USE PUBS
GO
DECLARE @Count int
EXEC @Count = spCountState3 'CA'
SELECT TotalState=@Count
GO

Akan menghasilkan pengembalian nilai sebagai berikut :
TotalState
15

Nah dengan ketiga cara tersebut maka anda bebas memilih teknik mana yang ingin anda implementasikan dalam program anda.

Semoga artikel ini bermanfaat bagi anda.

Demikian hasil yang diperoleh dan akhir dari isi tutorial ini.

Segala kritik, komentar mengenai artikel ini dapat disampaikan.

Best Regards,
Pieter

Rekursif UDF ( User Defined Function) Di dalam SQL Server 2000

By: pieter_edison@yahoo.com

Contoh Kasus.

Misalkan dalam suatu database anda, perusahaan anda memiliki kasus seperti contoh dibawah ini.

Perusahaan anda memiliki background penjualan spare part katakanlah pensil.

Untuk Pensil tersebut, perusahaan anda memiliki rekanan supplier dari luar negeri misalkan PT.X dari Korea, PT.Y dari Jepang, dan PT. Z dari supplier lokal.

PT.X yang menjadi supplier anda mengeluarkan bermacam-macam jenis pensil korea yang hendak anda jual kembali kepada customer anda.

PT.Y yang menjadi supplier anda mengeluarkan bermacam-macam jenis pensil jepang yang hendak anda jual kembali kepada customer anda.

PT.Z yang menjadi supplier anda mengeluarkan bermacam-macam jenis pensil buatan lokal yang hendak anda jual kembali kepada customer anda.

Dan kebetulan juga, perusahaan anda mendevelop aplikasi sendiri, dimana database anda yang anda simpan menggunakan SQL Server tentunya.

Untuk kasus ini, saya ambil contoh sederhana database anda menggunakan table master Pensil atau disingkat TblPensil yang isinya ada field Kode_Supplier, Kode_Pensil dan Nama_Pensil

Kode_Supplier : X à dimana kode_supplier ini memiliki reference ke table supplier

Kode_Pensil : A1

Nama_Pensil: PensilKorea1

Kode_Supplier : Y

Kode_Pensil : Y1

Nama_Pensil: PensilJepang1

Kode_Supplier : Z

Kode_Pensil : Z1

Nama_Pensil: PensilLokal1

Kemudian suatu saat, karena kebijaksaan internal supplier dari Jepang anda, untuk pensil dengan kode_pensil : Y1 dalam database anda, produk tersebut telah tidak diproduksi lagi, dan digantikan dengan produk baru Y2.

Tetapi karena di gudang anda dan stock masih ada di dalam database anda, produk Y1 ini masih tetap dijual kepada customer anda, dan dengan kondisi tertentu anda juga ingin meyakinkan kepada customer anda bahwa produk Y2 ini sebenarnya sama dengan Y1 atau istilah kerennya “common part” atau barang pengganti.

Untuk keperluan ini, anda terpaksa me-redesign database master anda, dan menambahkan field baru after part dengan design sebagai berikut :

Kode_Supplier : X

Kode_Pensil : A1

Nama_Pensil: PensilKorea1

Common_Part : NULL

Kode_Supplier : Y

Kode_Pensil : Y1

Nama_Pensil: PensilJepang1

Common_Part: Y2

Kode_Supplier : Y

Kode_Pensil : Y2

Nama_Pensil: PensilNewJepang2

Common_Part: NULL

Kode_Supplier : Z

Kode_Pensil : Z1

Nama_Pensil: PensilLokal1

Common_Part : NULL

Nah, dari kasus diatas, mungkin saja untuk kasus tertentu, ada banyak common-part yang terjadi dalam kasus anda.

Misalkan seperti dibawah ini :

Kode_Pensil Common_Part

Y1 Y2

Y2 Null

Z1 Z2

Z2 Z3

Z3 Null

Dari kasus diatas, anda ingin menampilkan dalam kode program anda ( client rules ), baik menggunakan program asp atau vb atau sebagainya.

Anda ingin menampilkan ketika user mengetik kode_pensil Z1 maka akan terdapat common-part apa saja yang berhubungan dengan kode pensil Z1 tersebut.

Untuk kasus seperti ini maka, di dalam SQL Server hal ini dapat kita atasi dengan menggunakan User Defined Function SQL Server secara rekursif.

UDF SQL Server secara rekursif maksudnya adalah ketika dalam suatu kasus kita memerlukan pemanggilan kembali fungsi tersebut hal ini dapat kita lakukan dengan memanggil kembali fungsi yang telah kita buat tersebut disertai dengan parameter input yang digunakan juga dalam SQL Server. Dan sebagai tambahan, ketika UDF rekursif tersebut dalam posisi “looping terus-menerus” dalam hal ini, tidak ditemukan data yang sama atau ada isi data yang salah, maka secara otomatis pada looping tertentu, SQL Server akan men-stop hal ini.

Isi table TblPensil Anda :

Kemudian untuk menampilkan User Defined Function yang menampilkan Common-Part Anda :

ALTER function Rekursif(

@Kode_Pensil varchar(10)

)

RETURNS @Tbl TABLE (

Common_Part varchar(10)

)

AS

BEGIN

DECLARE @Common_Part varchar(10)

SET @Common_Part=(SELECT Common_Part FROM TBLPENSIL WHERE Kode_Pensil=@Kode_Pensil)

IF @Common_Part IS NOT NULL AND (@Common_Part >'') and (@Common_Part <> @Kode_Pensil)

INSERT INTO @Tbl

SELECT Common_Part

FROM TblPensil

WHERE Common_Part=@Common_Part

UNION

SELECT Common_Part

FROM Rekursif(@Common_Part)

RETURN

END

Saya akan jelaskan logikanya sebagai berikut :

1.Pertama buat suatu fungsi yang isinya mengembalikan suatu table yang tentunya berisi nama common_partnya, dimana fungsi ini memiliki parameter input berupa nama kode_pensil.

n Potongan scriptnya

ALTER function Rekursif(

@Kode_Pensil varchar(10)

)

RETURNS @Tbl TABLE (

Common_Part varchar(10)

)

AS

BEGIN

2. Cari Common-Part dari parameter input yang dimasukkan user tersebut, kemudian tampung hasilnya kedalam variable local tsql anda.

Potongan scriptnya

DECLARE @Common_Part varchar(10)

SET @Common_Part=(SELECT Common_Part FROM TBLPENSIL WHERE Kode_Pensil=@Kode_Pensil)

3. Kemudian cek, jika varibel local common_part tersebut tidak bernilai null atau tidak memiliki nilai karakter kosong atau variable local common_part tersebut tidak sama dengan kode_pensil parameter input kita, maka dipastikan bahwa common_part untuk kode_pensil parameter input kita pasti ada nilainya.

Potongan scriptnya

IF @Common_Part IS NOT NULL AND (@Common_Part >'') and (@Common_Part <> @Kode_Pensil)

4. Jika memiliki nilai, maka kita masukkan ke dalam table kembalian kita common part dimana common part yang kita seleksi adalah common part yang memiliki nilai yang sama dengan variable local common part tersebut.

Potongan scriptnya

SELECT Common_Part

FROM TblPensil

WHERE Common_Part=@Common_Part

5. Kemudian kita memanggil fungsi yang telah kita buat tersebut sekali lagi ( disinilah teknik rekursif tersebut, untuk mengecek common part selanjutnya dimana parameter input kita telah berubah dari kode_pensil yang pertama sekali menjadi variable local common_part.

Potongan scriptnya

SELECT Common_Part

FROM Rekursif(@Common_Part)

6. Kemudian untuk teknik terakhir, hasil dari step 4 dan 5 kita gabungkan ke dalam table kembalian kita untuk menghasilkan jumlah record yang ada.

Potongan scriptnya

INSERT INTO @Tbl

SELECT Common_Part

FROM TblPensil

WHERE Common_Part=@Common_Part

UNION

SELECT Common_Part

FROM Rekursif(@Common_Part)

Sehingga kita mendapatkan keluaran sebagai berikut :

Untuk Parameter input Kode_Pensil Y1

SELECT * FROM Rekursif(‘Y1’)

maka akan menghasilkan output :

Common_Part

Y2

Sedangkan Untuk Parameter input Kode_Pensil Z1

SELECT * FROM Rekursif('Z1')

maka akan menghasilkan output :

Common_Part

Z2

Z3

Demikian hasil yang diperoleh dan akhir dari isi tutorial ini.

Best Regards,

Pieter