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', ',') )
Howdy! Someone in my Myspace group shared this
site with us so I came to look it over. I’m definitely loving the information. I’m bookmarking and
will be tweeting this to my followers! Exceptional blog
and brilliant style and design.