Parsing array parameter into SQL Stored Procedure isn’t as simple as it seen. The problem is SQL Server has not array parameter. To outsmart this problem, we can make a SQL Function to delimit our parameter before parsing it to Stored Procedurr, DataSet, etc.

Here’s a sample code of this SQL Function

CREATE FUNCTION [dbo].[fn_ParseDelimitedStrings](@String nvarchar(MAX), @Delimiter char(1))
RETURNS @Values TABLE
(
RowId int Not Null IDENTITY(1,1) PRIMARY KEY
,Value nvarchar(255) Not Null
)
AS
BEGIN
DECLARE  @startPos smallint
,@endPos smallint

IF (RIGHT(@String, 1) != @Delimiter)
SET @String = @String + @Delimiter
SET @startPos = 1
SET @endPos = CharIndex(@Delimiter, @String)
WHILE @endPos > 0
BEGIN
INSERT @Values(Value)
SELECT LTRIM(RTRIM(SUBSTRING(@String, @startPos, @endPos - @startPos)))
-- remove the delimiter just used
SET @String = STUFF(@String, @endPos, 1, '')
-- move string pointer to next delimiter
SET @startPos = @endPos
SET @endPos = CHARINDEX(@Delimiter, @String)
END
RETURN
END

Done! Now we have a powerful function to array parsing problem. Here’s sample code to use this function

SELECT * FROM Account WHERE Name IN (
SELECT Value FROM [fn_ParseDelimitedStrings]('abc,def,ghi,jkl', ',')
)