Home > SQL Tips, Stored Procedures, XML > Passing multiple/dynamic values to Stored Procedures & Functions | Part 2 – by passing XML

Passing multiple/dynamic values to Stored Procedures & Functions | Part 2 – by passing XML

September 9, 2012 Leave a comment Go to comments

In my previous post [Part 1] we saw how to pass multiple values to a parameter as a CSV string in an SP.

Here in the second part of this series we will use XML string that will contain the set of values and pass as an XML param variable to the SP. Then inside the SP we will parse this XML and use those values in our SQL Queries, just like we did in previous post with CSV string:

USE [AdventureWorks2012]

-- Create an SP with XML type parameter:
	@persons XML
	--DECLARE @persons XML
	--SET @persons = '<root><Name>Charles</Name><Name>Jade</Name><Name>Jim</Name><Name>Luke</Name><Name>Ken</Name></root>'

	SELECT T.C.value('.', 'NVARCHAR(100)') AS [Name]
	INTO #tblPersons
	FROM @persons.nodes('/root/Name') as T(C)
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate
	FROM [Person].[Person] PER
	WHERE EXISTS (SELECT Name FROM #tblPersons tmp WHERE tmp.Name  = PER.FirstName)
	ORDER BY FirstName, LastName

	DROP TABLE #tblPersons

-- Create XML string:
SET @xml = '<root>

-- Use the XML string as parameter which calling the SP:
EXEC uspGetPersonDetailsXML @xml
-- Check the output, objective achieved 🙂

-- Final Cleanup


This approach looks much cleaner and more stable to me than the previous one (CSV). XML is a de-facto standard to store and transmit data in a more structured way. Thus I prefer XML string over the CSV string on these type of cases.

The CSV approach is no different than this one, it internally converts the CSV string to an XML then parse it. Thus, like CSV if the XML string becomes lengthy it will also take time to parse the whole XML and then use the values in SQL queries in the SP.

In next [blog post] we will see how we can perform the same operation by using temporary (temp, #) tables.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: