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
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
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.
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.