How do make make a SQL Select statement using a CSV string as a conditional parameter in the "Where" clause?

You can use like:

WHERE ', ' + @PersonIds + ', ' like '%, ' + cast(PersonId as varchar(255)) + ', %'

This will not have good performance, because you cannot use an index.

If you want to take advantage of an index, you need to use dynamic SQL and construct an IN statement or split the string.

You can find a splitstring() function on the web and do something like this:

Select p.PersonId, p.FirstName, p.LastName
From   tblPerson p cross apply
       SplitString(@PersonIds, ', ') as s(val)
Where s.val = i.PersonId;

You can declare a table-valued function that breaks up a comma-separated list of integer values and converts it into a table:

CREATE FUNCTION tvf_StringToIntTable 
(
    -- Add the parameters for the function here
    @string VARCHAR(MAX), 
    @delimiter CHAR(1) -- could be ',', ';', etc
)
RETURNS 
@output TABLE 
(
    data int
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE @start < LEN(@string) + 1 
    BEGIN
        IF @end = 0 
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data) 
        VALUES(CONVERT(int, SUBSTRING(@string, @start, @end - @start)))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END 

    RETURN 
END
GO

then use this function in your select statement like this:

Select PersonId, FirstName, LastName
From   tblPerson
Where  PersonId in (select data from tvf_StringToIntTable(@PersonIds, ','))

EDIT:

In case of a very large string of values, then a more efficient splitting function should be used, e.g. this one.

If you only have a small number of records in the table that you are selecting from then it should be ok to do either of the other methods mentioned in the other answers. But to be scalable for larger volumes of rows, you should do this in two steps:

1. Split the CSV into a temp table (or table variable should work if the CSV list is fairly small)
2. Do an INNER JOIN on the local temp table to use it as a filter.

For example:

Declare @PersonIds varchar(500);
Set @PersonIds = '1, 3, 5';

CREATE TABLE #PersonsToSelect (PersonID INT NOT NULL PRIMARY KEY);

INSERT INTO #PersonsToSelect (PersonID)
   SELECT vals.SplitVal
   FROM   dbo.XmlOrClrSplitter(@PersonIDs, ',');

Select PersonId, FirstName, LastName
From   tblPerson per
INNER JOIN  #PersonsToSelect tmp
        ON  tmp.PersonID = per.PersonId;