Transform comma seperated string to rows in SQL Server

Use comma separated string in TSQL

  • Share

Passing complex data to and from stored procedures has always been a tricky one. The most elegant way to pass values to a stored procedure is to use predefined data types in SQL Server. Of course this means creating more than one object (Stored procedure + custom table data) in your database.

But, if you just need to pas an array of strings or, more often numbers, this can be achieved by passing a string value which is comma separated value (CSV), but it requires some modifications in your SP just to handle string value as a CSV.

This can be easily done with XML functions build in SQL Server. For the purpose of demo I'll use AdwentureWorks sample databse which can be found at CODEPLEX for doenalod at

Retrieving CSV value in stored procedure

Let's say you have relations one to many or many to many in your data model. To speed up reading data in your app, most efficient way is to do it i one goal. List of keys from secondary tables can be easily returned as a CSV string using XML functions.

        dg.City ,
        dg.CountryRegionCode ,
        dg.EnglishCountryRegionName ,
        dg.FrenchCountryRegionName ,
        dg.GeographyKey ,
        dg.PostalCode ,
        dg.SalesTerritoryKey ,
        dg.SpanishCountryRegionName ,
        dg.StateProvinceCode ,
        dg.StateProvinceName ,
        ( SELECT    ',' + CONVERT(VARCHAR(100), CustomerKey)
          FROM      dbo.DimCustomer dc
          WHERE     dc.GeographyKey = dg.GeographyKey
          ORDER BY  dc.CustomerKey
          XML PATH('')
        ) AS CustomerKeys
FROM    dbo.DimGeography dg

This will result the following data structure

Csv Select

The only isse is taht the values are comma leading strings, which should be handled in application, but in C# this can be aesily done using String.Split method overloads.

Passing CSV value to stored procedure

When in comes to passing CSV value to SP, it is a bit diffident in a way that you need to create a table object with one column from a string passed to SP. Again, this can be done using built in XML functions in SQL server.

DECLARE @CsvString NVARCHAR(500) ='11358,11749,11753,11918,11963,11997'
      SELECT @x = CAST('<A>'+ REPLACE(@CsvString,',','</A><A>')+ '</A>' AS XML)
      INSERT INTO @ValuesTable            
      SELECT t.value('.', 'int') AS inVal
      FROM @x.nodes('/A') AS x(t)

SELECT * FROM @ValuesTable

Result for the CsvString variable transformed to table will be following


Both approaches can be useful and I used them both when working with SP in C# using ADO. In case you are using Entity Framework, you are probably not going to need any of these because Entity Framework it elf does the mapping to object model and creates it's own SQL queries (a bit chunky ones but still fast enough) under the hub for you so you do not need to deal with any of these.

  • Share


Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.

Comments for this article

comments powered by Disqus