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