Select IN using varchar string with comma delimite

I am trying to search several tables for a list of phones.

The problem is converting the single string into a valid comma delimited string to use in conjunction with the IN clause.

I tried using replace to fix the problem.

DECLARE @PhoneNumber VARCHAR(3000) 
SET @PhoneNumber = '6725556666,2124444444'
SET @PhoneNumber = '''' + @PhoneNumber + ''''

SELECT @PhoneNumber
'6725556666','2124444444'

Finally the sample SQL does not recognize the string as expected:

SELECT  Provider
        ,PhoneNumber      
        ,ChangeType
        ,ChangeDate 
FROM dbo.PhoneLog
WHERE PhoneNumber IN (@PhoneNumber)

There are several ways to handle this. One option is to use dynamic sql and inject your phone number string into a variable containing the statement and then executing that like this:

DECLARE @PhoneNumber VARCHAR(3000) 
SET @PhoneNumber = '6725556666,2124444444'
DECLARE @SQL NVARCHAR(max)
SET @SQL = N'
    SELECT Provider, PhoneNumber, ChangeType, ChangeDate 
    FROM dbo.PhoneLog
    WHERE PhoneNumber IN (' + @PhoneNumber + ')'
EXEC sp_executesql @SQL

Please note that this approach can be vulnerable to SQL injection attacks, for instance feeding a string like

SET @PhoneNumber = '1);truncate table phonelog;--'

would effectively empty the table. So using a dynamic SQL approach like above should only be an option if it's certain that the string fed that in injected is sanitized and safe (or maybe it should never be used).

Another, possibly better, option is to use a user defined function to split the phonenumber variable and use that like this:

SELECT Provider, PhoneNumber, ChangeType, ChangeDate 
FROM dbo.PhoneLog
WHERE PhoneNumber IN (
    SELECT splitdata FROM dbo.fnSplitString(@PhoneNumber,',')
    -- you could add a check here that the data returned from the function 
    -- is indeed numeric and valid
    -- WHERE ISNUMERIC(splitdata) = 1
    )

Here's the function used in the example:

CREATE FUNCTION [dbo].[fnSplitString]   
    (   
        @string NVARCHAR(MAX),   
        @delimiter CHAR(1)   
    )   
    RETURNS @output TABLE(splitdata NVARCHAR(MAX)   
    )   
    BEGIN   
        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 (splitdata)    
            VALUES(SUBSTRING(@string, @start, @end - @start))   
            SET @start = @end + 1   
            SET @end = CHARINDEX(@delimiter, @string, @start)  

        END   
        RETURN   
    END

I did not write the function, I think I got it somewhere on the internet...