I recently found myself needing to pass a non-fixed sized set of Ids from an application to a stored procedure. A standard approach is to pass this data as a character string, and then split it again within the stored procedure.
I've done this in the past, but on this occasion I couldn't quickly locate my old code, so gave in and wrote it again, afterall it's only a few lines. So for future reference I have reproduced it here:
CREATE FUNCTION [dbo].[udfSplitCsv] (@data varchar(5000))
RETURNS @T TABLE (Id int)
AS
/*
Takes a CSV string parameter and returns the data as a table of integers.
*/
BEGIN
DECLARE @start int
DECLARE @pos int
DECLARE @part varchar(500)
DECLARE @id int
IF right(rtrim(@data),1) <> ',' SET @data = @data + ','
SET @start = 1
SET @pos = charindex(',', @data, @start)
WHILE @pos <> 0
BEGIN
SET @part = substring(@data, @start, @pos - @start)
INSERT INTO @T VALUES (@part)
SET @start = @pos + 1
SET @pos = charindex(',', @data, @start)
END
RETURN
END
The resulting table data can be included in queries, for example, using Northwind:
select OrderId, CompanyName, Country from Orders As O
inner join Customers As C
On O.CustomerID = C.CustomerID
inner join dbo.udfSplitCsv('10643,10692,10702,10926,10308') As I
On I.Id = O.OrderId