Beberapa T-SQL yang mungkin berguna untuk administrasi data dengan menggunakan SQL Server 2000 atau 2005.

1. sp_FindSPInSP
Store procedure dibawah ini digunakan untuk mencari suatu Store Procedure didalam Store Procedure. Artinya, jika didalam database anda, anda ingin mencari suatu store procedure "A" misalkan dipanggil di store procedure mana aja, maka gunakanlah sp dibawah ini.

--- Digunakan untuk mencari suatu Store Procedure didalam Store Procedure
--- EXEC sp_FindSPInSP 'MySP'
CREATE procedure [dbo].[sp_FindSPInSP]
(
@SPname varchar(3000)
)
AS
BEGIN

declare @id varchar(8000)

select @id=a.id
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype='p'
and a.[name]=@SPname

--print @id

select b.depid, a.[name]
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype='p' and b.depid=@id

END

2. sp_FindTableInSP
Store procedure dibawah ini digunakan untuk mencari suatu tabel tertentu didalam Store Procedure. Artinya, jika didalam database anda, anda ingin mencari suatu tabel "A" misalkan dipanggil di store procedure mana aja, maka gunakanlah sp dibawah ini.

--- Digunakan untuk Mencari Tabel tertentu ada di Store Procedure mana aja
--- EXEC sp_FindTableInSP 'MyTabel'
CREATE procedure [dbo].[sp_FindTableInSP]
(
@tablename varchar(3000)
)
AS
BEGIN
select b.depid, a.[name]
into #allsp
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype='p'

select a.name, a.id
into #alltable
from sysobjects a
where a.xtype='u' and a.name<>'dtproperties'

select tablename=t.name, spname=s.name
from #alltable t left join #allsp s
on t.id=s.depid
where t.name like '%'+@tablename+'%'
group by t.name, s.name
order by s.name asc

drop table #alltable
drop table #allsp

END

3. sp_FindFieldInSP
Store procedure dibawah ini digunakan untuk mencari suatu field tertentu didalam Store Procedure. Artinya, jika didalam database anda, anda ingin mencari suatu tabel "A" misalkan dipanggil di store procedure mana aja, maka gunakanlah sp dibawah ini.


--- Digunakan untuk Mencari suatu field tertentu ada di Store Procedure mana aja
--- EXEC sp_FindFieldInSP 'YourFieldName'
CREATE proc [dbo].[sp_FindFieldInSP]
@FieldName varchar(200)
AS

declare @idsp int

CREATE TABLE #Hasil
(
NamaSP varchar(500)
)

DECLARE CurA CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR

select distinct b.id from SysColumns a inner join sysdepends b
on a.id=b.depid
where a.name COLLATE SQL_Latin1_General_CP1_CS_AS=@FieldName

OPEN CurA

FETCH NEXT FROM CurA INTO @idsp

WHILE @@FETCH_STATUS=0
BEGIN

--print @idsp
INSERT INTO #Hasil
(NamaSP)
select [name] from SysObjects
where id=@idsp and xtype='P'
order by [name]

FETCH NEXT FROM CurA INTO @idsp

END

CLOSE CurA
DEALLOCATE CurA

select * from #Hasil

DROP TABLE #Hasil

4. sp_FindFieldInTable
Store procedure dibawah ini digunakan untuk mencari suatu field tertentu didalam Tabel. Artinya, jika didalam database anda, anda ingin mencari suatu field "A" misalkan digunakan ditabel mana aja, maka gunakanlah sp dibawah ini.

--- Digunakan untuk Mencari Field tertentu ada di Tabel mana aja
--- EXEC sp_FindFieldInTable 'MyColumn'
CREATE PROC [dbo].[sp_FindFieldInTable]
@ColName varchar(200)
as
begin
Select SO.Name as TableName, SC.name as ColumnName From
SysColumns SC Inner Join SysObjects SO on SO.ID=SC.ID
Where lower(SC.name) like @ColName
Order By TableName
end

5. sp_FindTableInFunction
Store procedure dibawah ini digunakan untuk mencari suatu tabel tertentu didalam Function. Artinya, jika didalam database anda, anda ingin mencari suatu tabel "A" misalkan digunakan difunction mana aja, maka gunakanlah sp dibawah ini.

--- DIGUNAKAN Untuk Mencari Tabel tertentu ada di Function mana aja
--- EXEC sp_FindTableInFunction 'MyTable'
CREATE procedure [dbo].[sp_FindTableInFunction]
(
@Tablename varchar(3000)
)
AS
BEGIN
select b.depid, a.[name]
into #allfunction
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype in ('FN','IF')

select a.name, a.id
into #alltable
from sysobjects a
where a.xtype='u' and a.name<>'dtproperties'

select tablename=t.name, functionname=fn.name
from #alltable t left join #allfunction fn
on t.id=fn.depid
where t.name like @Tablename
group by t.name, fn.name
order by fn.name asc

drop table #alltable
drop table #allfunction

END

6. sp_FindTableInView
Store procedure dibawah ini digunakan untuk mencari suatu tabel tertentu didalam View. Artinya, jika didalam database anda, anda ingin mencari suatu tabel "A" misalkan digunakan di View mana aja, maka gunakanlah sp dibawah ini.

--- Digunakan untuk Mencari Tabel tertentu ada di View mana aja
--- EXEC sp_FindTableInView 'MyView'

CREATE procedure [dbo].[sp_FindTableInView]
(
@Tablename varchar(3000)
)
AS
BEGIN
select b.depid, a.[name]
into #allview
from sysobjects a left join sysdepends b
on a.id=b.id
where a.xtype='V'

select a.name, a.id
into #alltable
from sysobjects a
where a.xtype='u' and a.name<>'dtproperties'

select tablename=t.name, viewname=v.name
from #alltable t left join #allview v
on t.id=v.depid
where t.name like @Tablename
group by t.name, v.name
order by v.name asc

drop table #alltable
drop table #allview

END

7. sp_FindWhatInTable
Store procedure dibawah ini digunakan untuk mencari suatu nilai ( Value ) tertentu didalam seluruh tabel yang ada didalam database anda. Artinya, jika didalam database anda, anda ingin mencari suatu field "A001" misalkan digunakan di mana aja didalam seluruh database anda, maka gunakanlah sp dibawah ini.

Note : Karena ini akan mencari keseluruhan tabel didalam database anda, maka tergantung skenario relationship dan performance database anda, karena akan mengakibatkan eksekusi program akan lama.

--- Digunakan untuk mencari Nilai didalam Field ( Value) didalam Seluruh Tabel
--- EXEC sp_FindWhatInTable '31000000'
ALTER proc sp_FindWhatInTable
@ValueData varchar(100)
as
begin
declare @tablename varchar(50)
declare @columname varchar(50)
declare @tablename2 varchar(50)
declare @columname2 varchar(50)
--declare @ValueData varchar(100)
declare @SqlSelect varchar(5000)

declare @ColumnNameX varchar(200)
DECLARE @SqlSelect2 VARCHAR(2000)
DECLARE @Varkosong VARCHAR(8)
set @Varkosong='KOSONG'
--set @ValueData='310WO200704005900SRV'

create table #A
(

ColumnName varchar(500),
TableName varchar(500)
)

select a.name, a.id
into #column
from SysColumns a inner join sysdepends b
on a.id=b.depid
inner JOIN systypes c on c.xusertype = a.xtype

select name, id
into #table
from sysobjects
where xtype='u' and name<>'dtproperties'

select distinct
tablename=a.name,
columnname=b.name
into #all
from #table a inner join #column b
on a.id=b.id
ORDER BY b.name

DECLARE CurA CURSOR LOCAL READ_ONLY FORWARD_ONLY
FOR

select tablename,columnname from #all

OPEN CurA

FETCH NEXT FROM CurA INTO @tablename, @columname

WHILE @@FETCH_STATUS=0
BEGIN

SET @SqlSelect='IF EXISTS (SELECT ' + @columname + ' FROM ' + @tablename +
' WHERE ' + @columname + ' LIKE ''%' + @ValueData + '%'''

SET @SqlSelect=@SqlSelect + ') INSERT INTO #A' + ' SELECT ''' + @columname + ''' , ''' + @tablename + ''' FROM ' + @tablename +
' WHERE ' + @columname + ' LIKE ''%' + @ValueData + '%'''

SET @SqlSelect=@SqlSelect + ' ELSE ' + 'INSERT INTO #A SELECT ''' + @VarKosong + '''' + ' , ' + '''' + @VarKosong + ''''
PRINT @SqlSelect

exec (@SqlSelect)

FETCH NEXT FROM CurA INTO @tablename, @columname

END

CLOSE CurA
DEALLOCATE CurA

--select * from #A

select DISTINCT TableName, ColumnName
INTO #B
from #A
WHERE TableName<>'KOSONG'

select * from #B

drop table #table
drop table #column
drop table #A
drop table #all
drop table #B

end

Demikian sedikit tutorial t-sql ini disharring, Semoga berguna untuk teman-teman khususnya yang berkecimpung didalam t-sql programming.

Regards,

Menampilkan record dari nomor tertentu dalam SQL Server 2000
By: pieter_edison@yahoo.com

Contoh Kasus :
Misalkan dalam suatu database anda, perusahaan anda memiliki database spare-part yang memiliki master customer ( pelanggan ).Kemudian dalam suatu kasus tertentu, anda ingin menampilkan record customer sebanyak 10 baris tetapi dengan nomor urut bukan dari 1 sampai x tertentu, tapi anda justru ingin menampilkan record pelanggan dari record 6 sampai ke 10 disebabkan data pelanggan ini memiliki kasus tertentu.

Kalau di MySQL dan Oracle dikenal dengan istilah LIMIT .

Hal ini dapat dilakukan dengan melakukan sedikit tip dan trik di SQL Server, khususnya SQL Server 2000.

Misalkan data Customer Anda adalah :

Tablename : Cust

ID CustName
01 DATA1
02 DATA2
03 DATA3
04 DATA4
05 DATA5
06 DATA6
07 DATA7
08 DATA8
09 DATA9
10 DATA10
11 DATA11
12 DATA12
13 DATA13

Anda ingin menampilkan data dari ID=05 sampai ID=11

Hal ini dapat dilakukan dengan :

Step 1 : Tampilkan dahulu data semua data sampai record ke 11

SELECT TOP 11 * FROM Cust ORDER BY [ID]

Akan menghasilkan record dari ID=1 sampai ID=11

Hasil
ID CustName
01 DATA1
02 DATA2
03 DATA3
04 DATA4
05 DATA5
06 DATA6
07 DATA7
08 DATA8
09 DATA9
10 DATA10
11 DATA11

Step 2 : Kemudian data ini dibuat alias sub quey sebagai data baru, data baru ini kita olah, dimana dari ID=1 sampai ID=11 ini, kita hanya ingin menampilkan data ID=05 sampai ID=11. Dengan kata lain kita ingin memfilter data ini sebanyak selisih 7 karena mulai dari ID=05 sampai ID=11.

Dengan query sqlnya sebagai berikut :

Syntax SQL : SELECT TOP 7 * FROM (
SELECT TOP 11 * FROM Cust ORDER BY [ID]
) A ORDER BY [ID] DESC

Dari query diatas, query Step 1 yang diatas kita simpan sebagai sub query dengan nama alias A, kemudian kita ambil 7 record dari alias table A tersebut, jangan lupa kita order dengan perintah DESC.

Akan menghasilkan record dari ID=05 sampai ID=11 dengan urutan DESC

Hasil :
ID CustName
11 DATA11
10 DATA10
09 DATA9
08 DATA8
07 DATA7
06 DATA6
05 DATA5


Step 3 : Setelah data tersebut terurut dan sesuai dengan step yang kita inginkan, selanjutnya data ini tinggal kita tampilkan kembali agar order datanya ASC dengan menggunakan sub query baru dari step 2 diatas.

Syntax SQL : SELECT * FROM
( SELECT TOP 7 * FROM (
SELECT TOP 11 * FROM Cust ORDER BY [ID]
) A ORDER BY [ID] DESC
) B ORDER BY [ID]

Akhirnya data tersebut akan terurut order sesuai dengan data yang kita minta.

Hasil :
ID CustName
05 DATA5
06 DATA6
07 DATA7
08 DATA8
09 DATA9
10 DATA10
11 DATA11


Demikian hasil yang diperoleh dan akhir dari isi tutorial ini.

Best Regards,
Pieter

Menampilkan Nomor Record tertentu dalam SQL Server 2000
By: pieter_edison@yahoo.com

Contoh Kasus :
Misalkan dalam suatu database anda, perusahaan anda memiliki database yang memiliki master table A.Kemudian dalam suatu kasus tertentu, anda ingin menampilkan record table A tersebut dengan nomor urut tertentu.

Contoh table A tersebut memiliki field : Nama dan Alamat. Dan ingin ditampilkan semua data Nama dan Alamat disertai dengan nomor urut letak data didalam table. Hal ini biasanya digunakan untuk penggunaan report atau nomor urutan grid (nomor baris grid) dalam kode program klient anda, misalkan dengan menggunakan gridview di VB.NET

Hal ini dapat dilakukan dengan melakukan sedikit tip dan trik di SQL Server, khususnya SQL Server 2000.

Misalkan data tabel A Anda adalah :

Tablename : A

Nama Alamat
Nama1 DATA1
Nama2 DATA2
Nama3 DATA3
Nama4 DATA4
Nama5 DATA5
Nama6 DATA6
Nama7 DATA7
Nama8 DATA8
Nama9 DATA9
Nama10 DATA10
Nama11 DATA11
Nama12 DATA12
Nama13 DATA13

Dan Hasil yang anda inginkan adalah sebagai berikut :

Tablename : A

Nomor Baris Nama Alamat
1 Nama1 DATA1
2 Nama2 DATA2
3 Nama3 DATA3
4 Nama4 DATA4
5 Nama5 DATA5
6 Nama6 DATA6
7 Nama7 DATA7
8 Nama8 DATA8
9 Nama9 DATA9
10 Nama10 DATA10
11 Nama11 DATA11
12 Nama12 DATA12
13 Nama13 DATA13

Hal ini dapat dilakukan dengan menggunakan 2 cara, menggunakan temporary table atau menggunakan deklarasi variable table.

Cara 1 : Menggunakan temporary table

Syntax SQL : SELECT IDENTITY(int,1,1) as [Nomor Baris], Nama, Alamat) INTO #X FROM A


Akan membuat nomor baris dari syntax IDENTITY dengan tipe int kedalam table temporary X dari table A

Selanjutnya anda tinggal menampilkan data tersebut dengan perintah :

SELECT * FROM #X

Jangan lupa untuk mendrop table temporary tersebut

DROP TABLE #X



Hasil
Nomor Baris Nama Alamat
1 Nama1 DATA1
2 Nama2 DATA2
3 Nama3 DATA3
4 Nama4 DATA4
5 Nama5 DATA5
6 Nama6 DATA6
7 Nama7 DATA7
8 Nama8 DATA8
9 Nama9 DATA9
10 Nama10 DATA10
11 Nama11 DATA11
12 Nama12 DATA12
13 Nama13 DATA13


Cara 2 : Menggunakan deklarasi table

Syntax SQL :

DECLARE @TEMP TABLE
([Nomor Baris] int identity,
Nama varchar(50),
Alamat varchar(50)
)

Kemudian anda tinggal menggunakan syntax insert table untuk mengisi data ke dalam table tersebut

Dengan query sqlnya sebagai berikut :

Syntax SQL :
INSERT INTO @TEMP
SELECT Nama, Alamat FROM A

Dan dari query diatas, anda tinggal menampilkan record dari Tabel yang sudah dideklarasi diatas yaitu @TEMP dengan menggunakan perintah sql SELECT.

Syntax SQL :
SELECT * FROM @TEMP

Hasilnya akan sama dengan cara 1 diatas.

Dari kedua cara diatas, jika anda ingin menampilkan nomor baris yang ganjil atau yang genap saja, bisa dengan menggunkan logika nomor baris dibagi 2 ( module ) yang mana sisanya = 0

Contoh untuk Bilangan Ganjil dari cara 1:

SELECT * FROM #X
WHERE [Nomor Baris] % 2 <>0

Contoh untuk Bilangan Ganjil dari cara 2:

SELECT * FROM @TEMP
WHERE [Nomor Baris] % 2 <>0

Contoh untuk Bilangan Genap dari cara 1:

SELECT * FROM #X
WHERE [Nomor Baris] % 2 =0

Contoh untuk Bilangan Genap dari cara 2:

SELECT * FROM @TEMP
WHERE [Nomor Baris] % 2 =0

Demikian hasil yang diperoleh dan akhir dari isi tutorial ini.

Best Regards,
Pieter

Export diagram dalam SQL Server 2000
By: pieter_edison@yahoo.com

Contoh Kasus :
Misalkan anda menggunakan SQL Server 2000 untuk keperluan data anda, kemudian suatu saat database anda telah aktif dimana dalam database ini, anda mendesign database juga menggunakan diagram relationship agar konsistensi dan integrasi data anda tidak kacau.

Suatu saat, anda membuat database baru yang hamper sama persis struktur table dan datanya sama seperti struktur database diatas.

Dikarenakan hal-hal tertentu, anda ingin meng-export seluruh diagram relationship yang telah anda buat tersebut ke database baru anda.

Hal ini bisa dan tidak mustahil dilakukan di SQL Server 2000.

Triknya sebagai berikut :

1. Pilih All Task, export data di nama database anda.
2. Masukkan data source dan destination anda terlebih dahulu
3. Pada pilihan Specifiy Table or Copy Query, pilih use a query to specify the data transfer
4. di Query statement, anda ketikkan kode berikut :
SELECT * FROM dtproperties
5. di pilihan Select Source Tables and Views rubahlah nama Result di destination column anda menjadi dtproperties
6. Kemudian secara otomatis diagram dari database pertama anda akan dicopy ke database baru anda.

Demikian hasil yang diperoleh dan akhir dari isi tutorial ini.

Best Regards,
Pieter

Order By didalam view dan function dalam SQL Server 2000
By: pieter_edison@yahoo.com

Contoh Kasus :
Misalkan anda menggunakan SQL Server 2000 untuk keperluan data anda, kemudian
Didalam database anda terdapat suatu table A dan table B dimana field ID di Tabel A tersebut relationship dengan field IDCol di TabelB

Tabel A tersebut terdiri dari Field : ID, Nama

Tabel B tersebut terdiri dari Field : IDCol, NamaEx

Suatu saat, anda disuruh untuk mendesign dan menarik informasi dari kedua table tersebut ( Tabel A dan table B) tersebut dengan pengurutan berdasarkan Field NamaEx dari Tabel B dimana anda menggunakan view atau function didalam kode sql anda.

CREATE VIEW GABUNG
AS
SELECT A.ID, A.Nama, B.IDCol, B.NamaEx FROM A A INNER JOIN B B
ON A.ID=B.IDCol
ORDER BY B.NamaEx

Atau bisa juga anda menggunakan function dimana function anda mengembalikan table.

Jika anda menggunakan syntax diatas dan anda execute query hasil tersebut akan menampilkan informasi pesan kesalahan :

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Hal ini disebabkan karena syntax ORDER BY tidak diperbolehkan didalam penggunaan function ataupun view.

Terus mungkin anda bisa mengakalinya dengan meng-order data tersebut di kode client anda..

Sebenarnya syntax ORDER BY ini bisa digunakan dalam VIEW ataupun FUNCTION,
Tetapi sekali lagi kita menggunakan trik tertentu.

Trik tersebut kita tambahkan perintah TOP 100 PERCENT saja, maka otomatis VIEW OR FUNCTION anda akan diorder.

Coba anda ganti view diatas dengan kode sebagai berikut :

CREATE VIEW GABUNG
AS
SELECT TOP 100 PERCENT A.ID, A.Nama, B.IDCol, B.NamaEx FROM A A INNER JOIN B B
ON A.ID=B.IDCol
ORDER BY B.NamaEx

Maka syntax sql anda akan dinyatakan benar, dan hasil yang diperoleh data anda akan diorder sesuai dengan permintaan anda tanpa ada kesalahan sintaks lagi.


Demikian hasil yang diperoleh dan akhir dari isi tutorial ini.

Best Regards,
Pieter

Menghapus nilai null dalam SQL Server 2000

By: pieter_edison@yahoo.com

Contoh Kasus :

Misalkan anda menggunakan SQL Server 2000 untuk keperluan data anda, kemudian

Didalam database anda terdapat suatu table A

Tabel A tersebut terdiri dari Field : ID, Nama

Yang isinya terdiri dari

ID Nama

01 NULL

02 B

03 A

04 D

05 NULL

06 F

Kemudian anda di suruh untuk menampilkan data yang diorder berdasarkan field Nama dimana data NULL ingin anda letakkan di baris paling bawah

Jika anda menggunakan syntax seperti berikut :

Select * from A

Order by Nama

Akan menghasilkan data :

ID Nama

01 NULL

05 NULL

03 A

02 B

04 D

06 F

Terdapat nilai null di field nama.

Agar nilai NULL diletakkan di baris paling bawah ketika menampilkan data, maka anda harus mengganti syntax sql anda diatas dengan trik sebagai berikut :

Select * from A

Order by CASE

WHEN Nama IS NULL THEN 1

ELSE O

END

Maka akan menghasilkan data dengan nilai NULL diabaikan.

ID Nama

03 A

02 B

04 D

06 F

01 NULL

05 NULL

Demikian hasil yang diperoleh dan akhir dari isi tutorial ini.

Best Regards,

Pieter

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