Sometimes we need to pass an array to the Stored Procrdure and split the array inside the stored proc. For example, lets say there is a datagrid displaying sales orders, each sales order associated with an orderid (PK in the Sales table). If the user needs to delete a bunch of sales orders ( say 10-15 etc)..it would be easier to concatenate all the orderid's into one string like 10-24-23-34-56-57-....etc and pass it to the sql server stored proc and inside the stored proc, split the string into individual ids and delete each sales order.

There can be plenty of other situations where passing a delimited string to the stored proc is faster than making n number of trips to the server.

CREATE PROCEDURE ParseArray (@Array VARCHAR(1000),@separator CHAR(1))
AS 
BEGIN
SET NOCOUNT ON
– @Array is the array we wish to parse
– @Separator is the separator charactor such as a comma
    <span class="kwrd">DECLARE</span> @separator_position <span class="kwrd">INT</span> <span class="rem">-- This is used to locate each separator character</span>
    <span class="kwrd">DECLARE</span> @array_value <span class="kwrd">VARCHAR</span>(1000) <span class="rem">-- this holds each array value as it is returned</span>

– For my loop to work I need an extra separator at the end. I always look to the
– left of the separator character for each array value
    <span class="kwrd">SET</span> @<span class="kwrd">array</span> = @<span class="kwrd">array</span> + @separator

– Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
– patindex matches the a pattern against a string
SELECT  @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT  @array_value = LEFT(@array, @separator_position - 1)
– This is where you process the values passed.
            <span class="rem">-- Replace this select statement with your processing</span>
            <span class="rem">-- @array_value holds the value of this element of the array</span>
            <span class="kwrd">SELECT</span>  Array_Value = @array_value
            <span class="rem">-- This replaces what we just processed with and empty string</span>
            <span class="kwrd">SELECT</span>  @<span class="kwrd">array</span> = STUFF(@<span class="kwrd">array</span>, 1, @separator_position, <span class="str">&#39;&#39;</span>)
        <span class="kwrd">END</span>

SET NOCOUNT OFF
END
GO