I'm torn between referring you to SO or answering it here, 'cos this is almost a programming question. But since I've already got a solution I use ... I'll post that ;)
The way this one works is you feed a comma delimited string (simple split, doesn't do CSV style splits) into the sproc as a varchar(4000) and then feed that list into this function and get a handy table back out, a table of just varchars.
This allows you to send in the values of just the ids that you want processed, and you can do a simple join at that point.
Alternately you could do something with a CLR DataTable and feed that in, but that's a bit more overhead to support and everyone understands CSV lists.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER FUNCTION [dbo].[splitListToTable] (@list nvarchar(MAX), @delimiter nchar(1) = N',')
RETURNS @tbl TABLE (value varchar(4000) NOT NULL) AS
This guy is apparently THE guy in SQL arrays and lists
Need an easy non-dynamic way to split a list of strings on input for comparisons
Usage like thus:
DECLARE @sqlParam VARCHAR(MAX)
SET @sqlParam = 'a,b,c'
SELECT * FROM (
select 'a' as col1, '1' as col2 UNION
select 'a' as col1, '2' as col2 UNION
select 'b' as col1, '3' as col2 UNION
select 'b' as col1, '4' as col2 UNION
select 'c' as col1, '5' as col2 UNION
select 'c' as col1, '6' as col2 ) x
WHERE EXISTS( SELECT value FROM splitListToTable(@sqlParam,',') WHERE x.col1 = value )
DECLARE @endpos int,
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @startpos = 0
SET @endpos = charindex(@delimiter, @tmpstr)
WHILE @endpos > 0
SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
@endpos - @startpos - 1)))
INSERT @tbl (value) VALUES(@tmpval)
SET @startpos = @endpos
SET @endpos = charindex(@delimiter, @tmpstr, @startpos + 1)
SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
INSERT @tbl(value) VALUES (ltrim(rtrim(@leftover)))