Passing multiple, comma-delimited, parameters to a stored procedure is tricky. I found the need for this trick while trying to pass multiple parameters from a web form to my Microsoft SQL Reporting Services report.

Let’s say, for instance, you would like to filter employee data by a series of employee numbers:

DECLARE @EmployeeIDS nvarchar(50)
SET @EmployeeIDS = ‘15000, 34587, 20989, 987665’

SELECT *
FROM Employees
WHERE EmployeeID IN (@EmployeeIDS)

One would think this would work. However, it does not. The IN operator cannot accept a comma-delimited string. Too bad. It would make things a lot easier if it did.

4GuysFromRolla came up with this solution around this problem.

Essentially, they create a user-defined function that receives a comma-delimited string and converts it into a table. The IN operator loves it some tables.

Here’s the function:

CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue TABLE
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGINWhile (Charindex(@SplitOn,@List)>0)
Begin

INSERT INTO @RtnValue (value)
SELECT
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
INSERT INTO @RtnValue (Value)
SELECT Value = ltrim(rtrim(@List))
RETURN
END

You can now use this function like so:

DECLARE @EmployeeIDS nvarchar(50)
SET @EmployeeIDS = ‘15000, 34587, 20989, 987665’

SELECT *
FROM Employees
WHERE EmployeeID IN (SELECT convert(int, Value) FROM dbo.Split(@EmployeeIDS,’,’))

The Split function returns a table to the IN operator:

Value
15000
34587
20989
20989
987665

 

Furthermore, because you are using a SELECT statement with the Split function, you could apply additional filters to remove specified parameters that you would like to ignore:

DECLARE @EmployeeIDS nvarchar(50)
SET @EmployeeIDS = ‘15000, 34587, 20989, 987665’

SELECT *
FROM Employees
WHERE EmployeeID IN (SELECT convert(int, Value) FROM dbo.Split(@EmployeeIDS,’,’) WHERE Value NOT IN (SELECT EmployeeID FROM [Table_Terminated_Employees])