Question: > 
> **Possible Duplicate:**  
> 
> [Help with a sql search query using a comma delimitted parameter](https://stackoverflow.com/questions/5426499/help-with-a-sql-search-query-using-a-comma-delimitted-parameter) 
> 
> 
> 

I want to write a stored procedure that performs a select on a table and need one input variable of type `varchar(max)`. 

I'd like to send a bunch of values separated by `,` as the input parameter, e.g.

```
'Jack','Jane','Joe'

```

and then get the rows that contain one of these names. 

In SQL the code would be 

```
Select * from Personnel where Name in ('Jack','Joe','Jane');  

```

Now I want to have a variable in my C# app, say strNames and fill it like 

```
string strNames = "'Jack','Joe','Jane'";

```

and send this variable to the SP and execute it. Something like 

```
Select * from Personnel where Name in (''Jack','Joe','Jane'') -- this is wrong

```

But **how can I tell SQL Server to run such command?** 

I need to make this happen and I know it's possible, please give me the clue.

Answer: 
First of all, the single names don't need to be quoted when you pass them to the stored procedure.

```
using (SqlCommand cmd = new SqlCommand("MyStoredProc", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@longFilter", "Jack,Jill,Joe");

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        ...
    }
}

```

Then, in the stored procedure, you can use simple text functions and a temporary table as follows to split up the string at the commas and an an entry to the temporary table for each part of the string:

```
DECLARE @temp AS TABLE (Name NVARCHAR(255))

IF ISNULL(@longFilter, '') <> ''
BEGIN
    DECLARE @s NVARCHAR(max)
    WHILE LEN(@longFilter) > 0
    BEGIN
        IF CHARINDEX(',', @longFilter) > 0
        BEGIN
            SET @s = LTRIM(RTRIM(SUBSTRING(@longFilter, 1, CHARINDEX(',', @longFilter) - 1)))
            SET @longFilter = SUBSTRING(@longFilter, CHARINDEX(',', @longFilter) + 1, LEN(@longFilter))
        END ELSE
        BEGIN
            SET @s = LTRIM(RTRIM(@longFilter))
            SET @longFilter= ''
        END

        -- This was missing until 20140522
        INSERT INTO @temp (Name) VALUES (@s)
    END
END

```

Later use the following `SELECT` to get a list of all people the name of which is in `@temp` or all of them if `@temp` doesn't contain any rows (unfiltered result):

```
SELECT * FROM Personnel WHERE Name IN (SELECT Name FROM @temp) OR (SELECT COUNT(*) FROM @temp) = 0

```
You could simply check if Name is contained in the string. Note the commas at the start of the end to ensure you match the full name

```
string strNames = ",Jack,Joe,Jane,";

```

The the SQL becomes

```
select * from Personnel where PATINDEX('%,' + Name + ',%', @strNames) > 0

```

See <http://www.sqlfiddle.com/#!3/8ee5a/1>