It is a very common requirement among the SQL Server developer community, how to pass multi-value strings to a stored procedure. As we all know multi-values are totally against the basic foundation of relational model and hence in T-SQL there are certain tricks you can apply to get them done. Note that some of the methods described here may not suit your requirements as it is and may use non-relational and proprietory methods. Also there may be some undocumented methods and the relevant caveats apply. Basic recommendations like use proper datatype conversion techniques, not using SELECT *, not ordering columns by positional numbers etc must be considered for stable production code.

Now coming to the methods of parsing an array and using it for data manipulations, the lion's share of "array" usage in stored procedure comes to insert data as multiple rows into tables. Here are some tricks, where @param is assumed to be a CSV. You can pass any delimiter and improvise the methods accordingly.

: @param format should be 'item1,item2,...,itemn'; for example '1,2,3,4,5' or 'ab,cde,fgh,ijklm,n'

For simple comparisons, there is no need for complicated routines. The inherent pattern matching methods can be used in many cases. One prime example is to pass in a list of values and use it in the IN list of a WHERE clause. Here are some common methods,

  • Using the CHARINDEX function
    	SELECT *
    	  FROM tbl
    	 WHERE CHARINDEX(',' + CAST(col AS VARCHAR) + ',', ',' + @param + ',') > 0
    
  • Using PATINDEX, can be used on columns with text/image datatypes
    	SELECT *
    	  FROM tbl
    	 WHERE PATINDEX('%,' + col + ',%', ',' + @param + ',') > 0	 
    
  • Using the LIKE operator
    	SELECT *
    	  FROM tbl
    	 WHERE ',' + @param + ',' LIKE '%,' + CAST(col AS VARCHAR) + ',%'
    
  • Using Dynamic SQL with IN list in the WHERE clause (Note that this is not always recommended due to obvious reasons)
    	EXEC('SELECT * FROM tbl WHERE col IN (' + @param + ')')
    
  • A very highly recommended method is to use a Number/Sequencing table. Refer to: Creating a table of sequential Numbers : @param format should be 'item1,item2,...,itemn'; for example '1,2,3,4,5' or 'ab,cde,fgh,ijklm,n'

  • Using the Numbers as arguments for the SUBSTRING
    	SELECT CASE WHEN SUBSTRING(',' + @param + ',', number, 1) = ','
    				THEN LTRIM(RTRIM(SUBSTRING(',' + @param + ',', number + 1, 
    				CHARINDEX(',', ',' + @param + ',', number + 1) - number - 1)))
    	       END AS [item]
    	  FROM Numbers
    	 WHERE Number BETWEEN 1 AND LEN(',' + @param + ',') - 1
    	   AND SUBSTRING(',' + @param + ',', Number, 1) = ','
    
  • Same as above, but more concise and easy to comprehend.
    	SELECT SUBSTRING(',' + @param + ',', Number + 1,
    				CHARINDEX(',', ',' + @param + ',', Number + 1)  
    				- Number - 1) AS [item]
    	  FROM Numbers
    	 WHERE SUBSTRING(',' + @param + ',', number, 1) = ','
    	   AND Number < LEN(',' + @param + ',')
    
  • This method uses a self-join and gives you the sequencing position as well. You can re-write the following as a subquery as well.
    	SELECT SUBSTRING(',' + @param + ',', MAX(n1.Number + 1), n2.Number 
    				- MAX(n1.Number + 1)) AS [item],
    	       COUNT(n2.Number) AS [position]
    	  FROM Numbers n1
    	 INNER JOIN Numbers n2 
    	    ON n1.Number < n2.number 
    	   AND n2.Number <= LEN(',' + @param + ',') + 1 
    	 WHERE SUBSTRING(',' + @param + ',', n1.Number, 1) = ','
    	   AND SUBSTRING(',' + @param + ',', n2.Number, 1) = ','
    	 GROUP BY n2.Number 
    
  • The following method also gives you the positional value and is simpler
    	SELECT SUBSTRING(@param, Number, CHARINDEX(',', @param + ',', Number) 
    				- Number) AS [item],
    		   1 - LEN(REPLACE(LEFT(@param, Number), ',', SPACE(0))) 
    				+ Number AS [position]
    	  FROM Numbers  
    	 WHERE SUBSTRING(',' + @param, Number, 1) = ','
    	   AND Number < LEN(@param) + 1
    
  • Again another way, similar logic.
    	SELECT SUBSTRING(@param, Number + 1, CHARINDEX(',', @param, Number + 1) 
    				- (Number + 1))
    	  FROM Numbers
    	 INNER JOIN (SELECT ',' + @param + ',') D(Param)
    	    ON Number <= LEN(@param)
    	   AND SUBSTRING(@param, Number, LEN(@param)) LIKE ',_%'
    
  • You can wrap any of these methods into a table valued UDF or another stored procedure and make it more reusable and handy. Apart from these set based approaches there are certain other tricks which can be used in T-SQL. Basically the following methods uses Dynamic SQL, a bit different, but can be used as an approach for smaller string parsing requirements in certain cases.

  • Replacing the element delimiters with ' UNION SELECT '. The results can be stored in a #temp table and use it in subsequent operations.
    	CREATE TABLE #temp (Item INT NOT NULL PRIMARY KEY)
    	GO
    	DECLARE @SQLx VARCHAR(8000)
    	SET @SQLx = 'SELECT ' + REPLACE(@param, ',', ' UNION SELECT ')
    	INSERT #temp EXEC(@SQLx)
    
  • A similar approach can be used with using separate INSERT statements as well.
    	DECLARE @SQLx VARCHAR(8000)
    	SET @SQLx = 'INSERT #temp VALUES (' + REPLACE(@param, ',', ')' + CHAR(13) + 
    				'INSERT #temp VALUES (') + ')'
    	EXEC(@SQLx)
    
  • A method which is getting very much hyped and praised, is the OPENXML method. This method, not at all efficient for larger datasets, but can be effectively used for relatively small number of items. The basic idea is to pass the values as an XML document to the stored procedure instead of a CSV. Here is an example.
    	DECLARE @doc VARCHAR(500)
    	DECLARE @XMLDoc INT
    	SET @doc = '<ROOT>
    			<Array Id="1" Value="someval1"></Array>
    			<Array Id="2" Value="someval2"></Array>
    			<Array Id="3" Value="someval3"></Array>
    		   </ROOT>'			
    	EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @doc
    	SELECT *
    	  FROM OPENXML (@XMLDoc , '/ROOT/Array', 1 )
    	          WITH (Id INT, Value varchar(15)) 
    	EXEC sp_xml_removedocument @XMLDoc
    
  • Another popular method is to use a procedural WHILE loop. Very common among newbie programmers by virtue of its simplicity, this method is neither efficient for larger datasets nor considered 'technocratic'. Here is an example:
    	WHILE LEN(@param) > 0
    	BEGIN 
    		IF CHARINDEX(',', @param) > 0 
    			SELECT @val = LEFT(@param, CHARINDEX(',', @param)  - 1) ,
    			       @param = RIGHT(@param, LEN(@param) - CHARINDEX(',', @param)) 
    		ELSE 
    			SELECT @val = @param, @param = SPACE(0)
    		EXEC('INSERT tbl VALUES (' + @val + ')') 
    	END
    
  • Be judicious and use common sense while using any of the string parsing routines in T-SQL. If you need more detailed analysis of these methods including performance considerations, refer to Erland Sommarskog's site. More ideas can be found from sites by Narayana Vyas, Umachandar Jayachandran and Steve Kass. If you have any suggestions, questions or remarks please email me at anith@bizdatasolutions.com