Pada waktu membuatt aplikasi yang didalamnya terdapat grid yang dapat dipilih beberapa data. Data tersebut kemudian akan diproses di stored procedure dalam sekali panggil, baik diupdate maupun delete. Kita butuh mempasing nilai dari data yang dipilih ke stored procedure. Yang jadi masalaha adalah SQL Server tidak menyediakan array sebagai parameter dalam stored procedure. Beberapa cara diantaranya adalah.
1. Gunakan keyword EXEC untuk menjalankan dynamic query dan memparsing nilai parameter dalam WHERE clause.
2. Gunakan XML sebagai parameter dengan keyword OPENXML untuk mengubah XML ke table.
Cara pertama memang sangat mudah tapi bukan cara yang baik untuk menggunakan dynamic query. SQL Server tidak dapat menyimpan statistic dari dynamic query. Cara kedua lebih baik tapi stored procedure menjadi lebih kompleks. Saya butuh cara yang lebih sederhana.
Cara lain adalah dengan membuat function yang akan merubah string dengan karakter pembatas menjadi sebuah table. Berikut adalah contoh fungsi tersebut.
CREATE FUNCTION [dbo].[fn_ParseDelimitedStrings](@String nvarchar(MAX), @Delimiter char(1))
RETURNS @Values TABLE
(
RowId int Not Null IDENTITY(1,1) PRIMARY KEY
,Value nvarchar(255) Not Null
)
AS
BEGIN
DECLARE @startPos smallint
,@endPos smallint
IF (RIGHT(@String, 1) != @Delimiter)
SET @String = @String + @Delimiter
SET @startPos = 1
SET @endPos = CharIndex(@Delimiter, @String)
WHILE @endPos > 0
BEGIN
INSERT @Values(Value)
SELECT LTRIM(RTRIM(SUBSTRING(@String, @startPos, @endPos - @startPos)))
-- remove the delimiter just used
SET @String = STUFF(@String, @endPos, 1, '')
-- move string pointer to next delimiter
SET @startPos = @endPos
SET @endPos = CHARINDEX(@Delimiter, @String)
END
RETURN
END
Pada stored procedure kita dapat gunakan fungsi tersebut untuk memfilter data seperti contoh berikut.
CREATE PROCEDURE GetCustomers
@customerIDs AS nvarchar
AS
BEGIN
DECLARE @custIDs AS table (Value nvarchar(255) Not Null)
INSERT INTO @custIDs
SELECT Value FROM [fn_ParseDelimitedStrings](@customerIDs, ',')
SELECT * FROM Orders WHERE CustomerID IN (SELECT Value FROM @custIDs)
END
Stored procedure menjadi sangat sederhana. Cukup mendeklarasikan memory table dan memasukkan hasil function ke dalam table tersebut yang kemudian dapat dijadikan sebagai filter dalam query.
Semoga bermanfaat. Versi Bahasa Inggris dari tulisan ini dapat dibaca di sini. (Biar gak ada yang komplain :p)