Transform comma seperated string to rows in SQL Server

Use comma separated string in TSQL

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 http://msftdbprodsamples.codeplex.com/releases/view/105902

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.

SELECT TOP 100
        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
        FOR
          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'
DECLARE @ValuesTable TABLE(Value BIGINT)
DECLARE @x XML 
      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

Csv2table

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.

Disclaimer

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.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

.NET

read more

JavaScript

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article