Break down a delimited string into a temporary table
<p>I am sending a delimited string to a 'Stored Procedure' which eventually will be used with an IN statement to update some records, so what im trying to do is create a 'Table-Valued Function' which will do this conversion and return this table that will be usable within this update statement but im unsure how to do this. </p>

<p>There is another function that breaks down delimited strings but it doesnt like like it returns an entire table, i dont really understand its sytax, its not any sql i have come accross before......</p>

<p>So (ill post the other function below) can i modify or use this function for an arbitrary amount of values in this delimited string or do i need to come up with another method?</p>

<pre><code>CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos &gt; 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
</code></pre>

<p>when using this function ill get each item with syntax like the following</p>

<pre><code>set @ID = (select Data from dbo.Split(@Params, '|') where ID = 1)
</code></pre>

<p>*****UPDATE WITH RESULTS FROM THE GIVEN ANSWER*******</p>

<pre><code>DECLARE @pVals VARCHAR(MAX)

SET @pvals = '1,2,3,4,5'

DECLARE @tblDelimitedData TABLE (
    [Data] INT NULL
    )

INSERT INTO @tblDelimitedData(data)
    SELECT data
    FROM dbo.Split(@pvals, ',') s
    WHERE ID &gt;= 2

SELECT * FROM @tblDelimitedData
</code></pre>

<p>Results are 2, 3, 4, 5</p>

<p>Perfect Thanks!!</p>