HOW to convert CSV to record set inside T-SQL?

Question

In my stored procedure I am passing a filter (using "WHERE Column IN" clause) as a parameter. The value of the parameter is given as CSV. What is the best method to convert this CSV in to a record set.

Example:-

SELECT * FROM Employee WHERE Name IN ('John','Joe','Jerry','James')

and I need to pass the names as a parameter which is a CSV string like

"John,Joe,Jerry,James"

.

Answer 1

Take a look at Erland Sommarskog's articles. He has in-depth information on the different ways of doing this kind of thing:

Arrays and Lists in SQL Server

Answer 2

Create a split string function

CREATE FUNCTION [dbo].[SplitString]
(
     @String VARCHAR(MAX) ,
     @Delimiter VARCHAR(10)
)
RETURNS @RetTable TABLE(
     String varchar(MAX)
)
AS 
BEGIN
    DECLARE @i INT ,
      @j INT
    SELECT  @i = 1
    WHILE @i <= LEN(@String)
    BEGIN
     SELECT @j = CHARINDEX(@Delimiter, @String, @i)
     IF @j = 0
     BEGIN
      SELECT @j = LEN(@String) + 1
     END
     INSERT @RetTable SELECT SUBSTRING(@String, @i, @j - @i)
     SELECT @i = @j + LEN(@Delimiter)
    END
    RETURN
END