Registered in England and Wales No: 08696309. VAT: 253 7691 79. Registered office 2 The Apex, Sheriffs Orchard, Coventry, CV1 3PP| CALL: 020 8050 1909

Passing a delimited string of parameters to MS SQL stored procedure

Passing a delimited string of parameters to MS SQL stored procedure

There is no easy way to use delimited string of parameters in the MS SQL. Imagine a situation where you have number of those in a format like ‘One,Two,Three’ etc. Since number of parameters are dynamic, the only way to use them all are to split them into a table.

There is no built-in function to split a delimited string in Microsoft SQL Server, but it is very easy to create your own. The following Table-Valued Function will split a string with a custom delimiter, and return the results as a table. This means you can easily use the output directly in a

JOIN

 with some other data.


<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">FUNCTION</span> [dbo].[<span class="hljs-keyword">SplitString</span>]
(
    @<span class="hljs-keyword">String</span> <span class="hljs-keyword">NVARCHAR</span>(<span class="hljs-number">MAX</span>),
    @Delimiter <span class="hljs-keyword">NCHAR</span>(<span class="hljs-number">1</span>)
)
<span class="hljs-keyword">RETURNS</span> <span class="hljs-keyword">TABLE</span>
<span class="hljs-keyword">AS</span>
<span class="hljs-keyword">RETURN</span>
(
    <span class="hljs-keyword">WITH</span> <span class="hljs-keyword">Split</span>(stpos,endpos)
    <span class="hljs-keyword">AS</span>(
        <span class="hljs-keyword">SELECT</span> <span class="hljs-number">0</span> <span class="hljs-keyword">AS</span> stpos, <span class="hljs-keyword">CHARINDEX</span>(@Delimiter,@<span class="hljs-keyword">String</span>) <span class="hljs-keyword">AS</span> endpos
        <span class="hljs-keyword">UNION</span> ALL
        <span class="hljs-keyword">SELECT</span> endpos+<span class="hljs-number">1</span>, <span class="hljs-keyword">CHARINDEX</span>(@Delimiter,@<span class="hljs-keyword">String</span>,endpos+<span class="hljs-number">1</span>)
            <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">Split</span>
            <span class="hljs-keyword">WHERE</span> endpos &gt; <span class="hljs-number">0</span>
    )
    <span class="hljs-keyword">SELECT</span> <span class="hljs-string">'Id'</span> = ROW_NUMBER() <span class="hljs-keyword">OVER</span> (<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-number">1</span>)),
        <span class="hljs-string">'Data'</span> = <span class="hljs-keyword">SUBSTRING</span>(@<span class="hljs-keyword">String</span>,stpos,<span class="hljs-keyword">COALESCE</span>(<span class="hljs-keyword">NULLIF</span>(endpos,<span class="hljs-number">0</span>),<span class="hljs-keyword">LEN</span>(@<span class="hljs-keyword">String</span>)+<span class="hljs-number">1</span>)-stpos)
    <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">Split</span>
)
<span class="hljs-keyword">GO</span>

To use

<span class="hljs-keyword">SplitString</span>
()

, just call it in a

SELECT

 as you would a normal table:


<span class="hljs-keyword">DECLARE</span> @DelimitedString <span class="hljs-keyword">NVARCHAR</span>(<span class="hljs-number">128</span>)
<span class="hljs-keyword">SET</span> @DelimitedString = <span class="hljs-string">'One,Two,Three,Four,Five'</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> dbo.<span class="hljs-keyword">SplitString</span>(@DelimitedString, <span class="hljs-string">','</span>)

This will split the string and output an ordered table with each value.

The output table has the column “Id” containing the original index of the value in the string. The column “Data” contains each string value. You can also use

Split()

 directly in a join with another table like this:


<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">Users</span>.[<span class="hljs-keyword">Name</span>] <span class="hljs-keyword">FROM</span> dbo.<span class="hljs-keyword">Users</span>
    <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> dbo.<span class="hljs-keyword">SplitString</span>(@DelimitedString, <span class="hljs-string">','</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">split</span>
        <span class="hljs-keyword">ON</span> <span class="hljs-keyword">Users</span>.[<span class="hljs-keyword">Name</span>] = <span class="hljs-keyword">split</span>.[<span class="hljs-keyword">DATA</span>]

Notice that

Split()

 is a feature added to T-SQL in Microsoft SQL Server 2005, and will not work on earlier versions.

Leave a Reply

Your email address will not be published. Required fields are marked *