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.
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......
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?
CREATE FUNCTION [dbo].[Split]
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
WHERE endpos > 0
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
set @ID = (select Data from dbo.Split(@Params, '|') where ID = 1)
DECLARE @pVals VARCHAR(MAX)
SET @pvals = '1,2,3,4,5'
DECLARE @tblDelimitedData TABLE (
[Data] INT NULL
INSERT INTO @tblDelimitedData(data)
FROM dbo.Split(@pvals, ',') s
WHERE ID >= 2
SELECT * FROM @tblDelimitedData
To insert the result of the function to a temporary table, you should use:
INSERT INTO #tempTable(data) SELECT data FROM dbo.Split(@Params, '|') s WHERE ID = 1
WHERE ID = 1, however, only inserts the first item in the delimited list. If you want to filter then number of values to be inserted in the table use
For example, you only want to insert the first three values, then you use:
WHERE ID <= 3
Additionally, your function to split is slower compared to some other available functions. I recommend that you a Tally-based splitter such as Jeff Moden's DelimitedSplit8K.