Archive

Posts Tagged ‘CSV String’

Pass an array of multiple values with a single Parameter in a Stored Procedure – SQL Server

July 10, 2015 3 comments

This post comes from an old discussion on MSDN T-SQL Forum[link], where someone asked about the same topic.

Check my reply there and here with this post I’m going to collate all the different methods I’ve blog previously that can be used for the same purpose.
 

Stored Procedures accept only a fixed and pre-defined number of parameters thus there is a limitation you cannot provide dynamic number of params, like you can do with other languages, like C, C#, Java, etc. In these languages there is concept of Arrays which you can pass in a method/function, but in SQL there are no array variables and it does not have any datatype that support arrays. Thus if you have to provide multiple values to any parameter you cannot do it directly, though there are some workarounds.
 

–> Here are some of the methods or workarounds by which we can pass multiple values as a single Parameter in a Stored Procedure or a Function:

Method #1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP or UDF, check here.

Method #2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP, check here.

Method #3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any parameter with the SP, check here.

Method #4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using user-defined table types. These TVPs can then be used to send multiple rows of data to SPs or UDFs, without creating a temp table or multiple parameters, check here.

Method #5 – Passing a JSON string: as a NVARCHAR datatype parameter. We will need to parse the JSON inside the SP, check here.
 

Thanks a lot, please provide your valuable comments and suggestions on this topic.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.

Check & Like my FB Page

Advertisement
Categories: Stored Procedures Tags: , , , , , ,

Combine multiple ROWS to CSV String… and vice-versa

September 16, 2010 16 comments

There are times when you want to combine values from multiple rows of a single column into a CSV (Comma Separates Value) String.

Like combining multiple orders of each customer into one single row, or multiple Cities against each State as shown below.

It was a bit difficult to do in SQL Server 2000 and requires to create a customized function or a script.

But with SQL Server’s 2005 version, the new “PATH” mode with “FOR XML” provides us a way to get the desired output. Its a bit tricky but will do the needful, that’s why I’ve also added this post for my reference as well.

CREATE TABLE #tempCityState (State VARCHAR(5), City VARCHAR(50))

INSERT INTO #tempCityState
SELECT 'CO', 'Denver' UNION
SELECT 'CO', 'Teluride' UNION
SELECT 'CO', 'Vail' UNION
SELECT 'CO', 'Aspen' UNION
SELECT 'CA', 'Los Angeles' UNION
SELECT 'CA', 'Hanford' UNION
SELECT 'CA', 'Fremont' UNION
SELECT 'WA', 'Seattle' UNION
SELECT 'WA', 'Redmond' UNION
SELECT 'WA', 'Bellvue'

SELECT * FROM #tempCityState

State	City
CA	Hanford
CA	Fremont
CA	Los Angeles
CO	Denver
CO	Aspen
CO	Vail
CO	Teluride
WA	Seattle
WA	Redmond
WA	Bellvue

 

Using the “FOR XML PATH” syntax:

SELECT DISTINCT State, (SELECT SUBSTRING((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),2,200000)) AS Cities
INTO #tempCityStateCSV
FROM #tempCityState t

-- OR --

SELECT DISTINCT State, (SELECT STUFF((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),1,1,'')) AS Cities
FROM #tempCityState t

SELECT * FROM #tempCityStateCSV

State	Cities
CA	Hanford,Fremont,Los Angeles
CO	Denver,Aspen,Vail,Teluride
WA	Seattle,Redmond,Bellvue

 

Now, converting it back to multiple rows:

SELECT A.[State], Split.a.value('.', 'VARCHAR(100)') AS City
FROM (SELECT [State], CAST ('<M>' + REPLACE([Cities], ',', '</M><M>') + '</M>' AS XML) AS String
	FROM  #tempCityStateCSV) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
ORDER BY 1,2

State	City
CA	Fremont
CA	Hanford
CA	Los Angeles
CO	Aspen
CO	Denver
CO	Teluride
CO	Vail
WA	Bellvue
WA	Redmond
WA	Seattle
Categories: SQL Tips Tags: , , ,