You need to convert the csv value into rows of a table for it to work inside the IN clause.
Here is a sample
DECLARE @Customer TABLE
(
CustomerId VARCHAR(100),
CustomerName VARCHAR(100)
)
INSERT INTO @Customer
SELECT 1, 'Bob' UNION ALL
SELECT 2, 'Steve' UNION ALL
SELECT 3, 'Smith' UNION ALL
SELECT 4, 'Matt' UNION ALL
SELECT 5, 'John'
SELECT * FROM @Customer
DECLARE @CustomerId VARCHAR(100)
SET @CustomerID = N'1,3,4'
SELECT * FROM @Customer WHERE CustomerId IN (@CustomerID)
SELECT * FROM @Customer WHERE CustomerId IN (SELECT VarcharValue FROM dbo.ufn_CsvToVarchar(@CustomerID))
This is the function used to convert csv values into rows.
CREATE Function dbo.ufn_CsvToVarchar ( @Array varchar(MAX))
returns @VarcharTable table
(VarcharValue varchar(50))
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(1000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @VarcharTable
Values (Cast(@array_value as varchar(50)))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end
Hope this helps.