In CSV string as parameter
Author Nigel Rivett
This is to perform the where clause
where id in (1,2,3)
but with the values to test as a paramter.
so we are looking to test
declare @csv varchar(20)
select @csv = '1,2,3'
where id in (@csv)
does not work because it will test the parameter as a single value - and try to convert it to an int
where 1 in (@csv)
gives an error because @csv cannot be converted to an int
where '1' in (@csv)
where '1,2,3' in (@csv)
returns true - and is the only statement that does (trailing white space excluded).
There are several ways of dealing with the csv parameter - which to use depends on the situation.
Simple where clause
where ',' + @csv + ',' like '%,' + convert(varchar(20),id) + ',%'
is a simple method but it cannot use any indexes so is inefficient for larcge tables.
Note the addition of leading and trailing ","'s to cater for multi digit values.
exec ('select * from tbl where id in (' + @csv + ')')
This can use an index on id in tbl but requires that the user has permission on tbl and can also suffer from injection.
I would not recommend this method.
Function to return resultset of values to test
see fn_ParseCSVString for the function
join dbo.fn_ParseCSVString(@csv, ',') f
on tbl.id = f.s
This can use an index on id in tbl so is probably more efficient at the expense of an extra object.
The resultset returned by the function could be included in the SP to populate a table variable or temp table.