Passing multiple/dynamic values to Stored Procedures & Functions | Part 2 – by passing XML
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] GO -- Create an SP with XML type parameter: CREATE PROCEDURE uspGetPersonDetailsXML ( @persons XML ) AS BEGIN --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 END GO -- Create XML string: DECLARE @xml XML SET @xml = '<root> <Name>Charles</Name> <Name>Jade</Name> <Name>Jim</Name> <Name>Luke</Name> <Name>Ken</Name> </root>' -- Use the XML string as parameter which calling the SP: EXEC uspGetPersonDetailsXML @xml GO -- Check the output, objective achieved š -- Final Cleanup DROP PROCEDURE uspGetPersonDetailsXML GO
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.
-
September 9, 2012 at 7:01 pmPassing multiple/dynamic values to Stored Procedures & Functions | Part3 – by using #table « SQL with Manoj
-
September 10, 2012 at 11:37 pmPassing multiple/dynamic values to Stored Procedures & Functions | Part4 ā by using TVP « SQL with Manoj
-
July 10, 2015 at 5:27 pmPassing multiple/dynamic values to Stored Procedures & Functions | Part1 – by passing CSV | SQL with Manoj
-
July 10, 2015 at 6:09 pmPassing multiple values with a single Parameter in a Stored Procedure – SQL Server | SQL with Manoj