Ahmad Masykur

Share your knowledge although one function!

About the author

Ahmad Masykur is a Software Architecture Engineer at PT. Freeport Indonesia Jakarta Indonesia.
In this blog, I share things of interest to me. Most topics are likely to be related to software development, but don't hold me to it.

Certificates



Awards


Powered by

Widget Prayer Time not found.

There is an error in XML document (4, 16278).X

Page List

Validators


Ahmad Masykur

Parsing Array Parameter in SQL Server Stored Procedure

If we develop application to select multiple data in grid and selected data to be process in stored procedure at same time, we need to pass the values of keys to stored procedure parameters. The problem is SQL Server has not array parameter. Here is some ways to resolve the problem.

  1. Use EXEC to execute dynamic query and passing the stored procedure parameter in WHERE clause.
  2. 2. Use XML as parameter and using OPENXML keyword to convert it to table.

First option is vary simple but it bad practice to use dynamic query. SQL Server can’t save statistic of dynamic query. Second option is better way but more complicated. We need simplest way to passing an array to stored procedure parameter.

Other option we can create table function to parse delimited string to table. Here is sample function to do that.

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

This function can be used in sample stored procedure below.

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

It very simple query. Just declare memory table and parse the delimited string into table to be used in select criteria.

I hope this tips is helpful for you.


Categories: SQL Server | Tips
Permalink | Comments (7) | Post RSSRSS comment feed

Comments

Ahmad Masykur | Reply

Tuesday, February 17, 2009 10:36 AM

trackback

Parsing Array Parameter pada SQL Server Stored Procedure

Pada waktu membuatt aplikasi yang didalamnya terdapat grid yang dapat dipilih beberapa data. Data tersebut

Urban Net Zone United States | Reply

Wednesday, March 04, 2009 9:47 PM

Urban Net Zone

so this is possible to do, thanks a lot for the code, maybe this what im looking for,i hope this gonna help me especially to my site

budong United States | Reply

Wednesday, March 04, 2009 9:48 PM

budong

its great that i found your site here, there so many thing i actually learn especially the right codes

thegar Indonesia | Reply

Monday, April 06, 2009 1:09 PM

thegar

mas bleh minta tolong..
buatin script sql.ttg parsing yang di atas tapi saya kasusnya sms gateway disini saya pakai gammu, jadi saya ingin agar setiap sms yang masuk dengan format contoh:pemilu[spasi]nama_partai[spasi]caleg[spasi]suara, dan sms ini akan dipecah menjadi beberapa table dan dimasukkan kedalam table yg baru. saya masih bru dalam hal ini jadi saya mohon petunjuknya. jika mas ahmad bersedia bisa langsung kirim ke email saya trims b4...

Humidifier Filters United Kingdom | Reply

Thursday, April 30, 2009 7:44 PM

Humidifier Filters

Very nice tips.it helped me a lot.thanks a lot.looking forward for more tips like this.

Enemy of Good Lebanon | Reply

Tuesday, May 19, 2009 5:28 PM

Enemy of Good

chek this link it should help you

www.codeproject.com/.../Array_as_Parameter.aspx

MojaMalenkost Slovenia | Reply

Wednesday, September 09, 2009 12:40 AM

MojaMalenkost

Thank you very much for the nice solution!

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading