Monday, 11 March 2013

Splitting a Comma Separated String in a Stored Procedure

In SQL, we might have a need to send a string of values to a stored procedure. We can do this neatly using XML, but we can also do this using a comma separated list of values. One technical difficulty with this solution is that, in the SQL Server, we need to split the string into its constituent parts.

A method which comes to mind is to do a replacement on the comma character and turn the string into an XML document, after which we can select the nodes:

The above would look very unsightly if we were to include it in every stored procedure. Therefore, we would turn it into a TABLE-VALUED function, after which we can call it multiple times from different stored procedures: