Stored procedure with variable number of parameters

You could pass it in as a comma-separated list, then use a split function, and join against the results.

CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN 
   (
       SELECT Item = CONVERT(INT, Item)
       FROM
       (
           SELECT Item = x.i.value('(./text())[1]', 'INT')
           FROM
           (
               SELECT [XML] = CONVERT(XML, '<i>' 
                    + REPLACE(@List, @Delimiter, '</i><i>') 
                    + '</i>').query('.')
           ) AS a
           CROSS APPLY
           [XML].nodes('i') AS x(i)
       ) AS y
       WHERE Item IS NOT NULL
   );

Now your stored procedure:

CREATE PROCEDURE dbo.doStuff
    @List VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cols FROM dbo.table AS t
        INNER JOIN dbo.SplitInts(@List, ',') AS list
        ON t.ID = list.Item;
END
GO

Then to call it:

EXEC dbo.doStuff @List = '1, 2, 3, ...';

You can see some background, other options, and performance comparisons here:

  • Split strings the right way – or the next best way
  • Splitting Strings : A Follow-Up
  • Splitting Strings : Now with less T-SQL
  • Comparing string splitting / concatenation methods
  • Processing a list of integers : my approach
  • Splitting a list of integers : another roundup
  • More on splitting lists : custom delimiters, preventing duplicates, and maintaining order
  • Removing Duplicates from Strings in SQL Server

On SQL Server 2016 or above, though, you should look at STRING_SPLIT() and STRING_AGG():

  • Performance Surprises and Assumptions : STRING_SPLIT()
  • STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
  • STRING_SPLIT() in SQL Server 2016 : Follow-Up #2
  • SQL Server v.Next : STRING_AGG() performance
  • Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions

SQLServer lets you pass TABLE parameter to the stored procedure. So you can define table type, CREATE TYPE LIST_OF_IDS AS TABLE (id int not null primary key), alter your procedure to accept a variable of this type (it should be readonly).