So you have a delimited list of values and you want to pass them to SQL as a single parameter and use them in a query.

One approach is to build the SQL statement dynamically somewhere in your code. Another is to pass the delimited list to a stored procedure and parse the string yourself with string operations and a cursor, then build dynamic SQL and use EXEC to get the results.

I usually want something more generic and reusable, which means a user defined function to handle the split and keep the rest of the SQL simple.

Say you have this SELECT statement:

SELECT ID,
       Name
FROM   MyTable
WHERE  Name IN ('Test1', 'Test2', 'Test3', 'Test4')

Nothing exciting. It returns the four records we asked for. But what if we do not know which records the user is going to ask for, and we need the statement to be parameterized?

Something like this would be nice, but as you probably know, it will not give you the results you need:

DECLARE @List VARCHAR(100)

SET @List = 'Test1, Test2, Test3, Test4'

SELECT ID,
       Name
FROM   MyTable
WHERE  Name IN (@List)

Hence the split function:

CREATE FUNCTION [dbo].[fnSplit]
(
    @List      VARCHAR(8000),
    @Delimiter VARCHAR(1)
)
RETURNS @Table TABLE
(
    ID    INT IDENTITY(1,1),
    Value VARCHAR(100)
)
AS
BEGIN

    -- loop through the list
    WHILE (CHARINDEX(@Delimiter, @List) > 0)
    BEGIN

        -- add the value to the table
        INSERT INTO @Table (Value)
        SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List) - 1)))

        -- remove the value from the list
        SET @List = SUBSTRING(@List,
                        CHARINDEX(@Delimiter, @List) + LEN(@Delimiter),
                        LEN(@List))

    END

    -- insert the remaining value from the list
    INSERT INTO @Table (Value)
    SELECT Value = LTRIM(RTRIM(@List))

    RETURN

END

The function uses a simple while loop that processes one value at a time, removes it from the string, and keeps going until there is nothing left. The result is a table with one row per value from the delimited string.

For example:

DECLARE @List      VARCHAR(100)
DECLARE @Delimiter VARCHAR(1)

SET @List      = 'Test1, Test2, Test3, Test4'
SET @Delimiter = ','

SELECT *
FROM   dbo.fnSplit(@List, @Delimiter)

And here is the earlier query rewritten to use it:

DECLARE @List      VARCHAR(100)
DECLARE @Delimiter VARCHAR(1)

SET @List      = 'Test1, Test2, Test3, Test4'
SET @Delimiter = ','

SELECT ID,
       Name
FROM   MyTable
WHERE  Name IN (SELECT Value FROM dbo.fnSplit(@List, @Delimiter))

Now you can pass in the delimited list and the delimiter and use it in a simple SQL statement to get the results you need.

One note from the present day: SQL Server 2016 added a built-in STRING_SPLIT function that covers this case, so on a modern instance you can skip the custom function entirely:

SELECT ID,
       Name
FROM   MyTable
WHERE  Name IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@List, ','))

The custom version above still has its uses. STRING_SPLIT takes a single character delimiter, and until SQL Server 2022 it did not return the ordinal position of each value, so if you need either of those things, a function of your own is still the way to go.