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...
