Beberapa bulan terakhir ini banyak artikel dan forum yang membahas mengenai Table-Valued Parameter (TVP). Saking hebohnya, ada yang menyebut TVP sebagai feature penting yang akan membuat banyak orang tertarik untuk menggunakan SQL Server 2008.
TVP adalah type parameter baru di SQL 2008 yang di-deklarasikan dengan menggunakan type data table yang kita definisikan. Kita dapat menggunakannya di dalam stored procedure atau function, tanpa sebelumnya harus membuat temporary table atau passing banyak parameter. Kita dapat menggunakannya pada saat melakukan Transact-SQL atau langsung dari aplikasi.
Ada kalanya dimana saya harus berulang kali meng-execute sebuah sp yang isinya satu baris data dari aplikasi karena validasi terhadap data disimpan di sp. Hal ini tentu menimbulkan round trip antara server dan client apabila misalnya pada saat si user melakukan proses penyimpanan data terdapat lebih dari 1 data yang harus divalidasi. Dengan TVP, saya hanya cukup satu kali mengeksekusi sp, dan mem-passing kesemua data di dalam TVP.
Pada contoh di bawah ini, saya akan membuat sebuah stored procedure untuk memvalidasi data, meng-insert data yang valid, dan mengembalikan data yang tidak valid. Tolong jangan liat sp-nya yang berantakan (hehe), tapi perhatikan bagian TVP-nya.
Pertama kita buat dulu table type-nya
|
create
type ProductType as
table (name varchar(20), quantity int, errormessage varchar(100))
|
Lalu kita buat stored procedure untuk meng-insert data, perhatikan bahwa tvp harus digunakan hanya sebagai input dan readonly sehingga kita tidak dapat melakukan transaksi insert, update atau delete terhadap parameter tersebut di dalam sp.
|
create
procedure InsertProduct
@product as ProductType readonly
as
declare @goodProduct as ProductType
declare @badProduct as ProductType
insert
into @goodProduct
(name, quantity)
select name, quantity from @product where quantity between 1 and 150
insert
into @badProduct
(name, quantity,errormessage)
select name, quantity,
'quantity should be between 1 and 150'
as errormessage from @product where quantity not
between 1 and 150
select
*
from @badproduct
|
Lalu kita test sp nya dengan statement di bawah ini
|
declare @product as ProductType
insert
into @product
(name, quantity)
values ('prod1',100),('prod2',200),('prod3',0)
exec InsertProduct
@product
|
Pada saat di-execute, sp nya akan mengembalikan data yang tidak valid
Untuk definisi lebih detail, silakan baca di BOL atau MSDN.