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

Passing multiple/dynamic values to Stored Procedures & Functions | Part 1 – by passing CSV

September 9, 2012 Leave a comment Go to comments

Here in the first Part of this series we will use a CSV string that will contain comma separated list of values and pass as a VARCHAR datatype param variable to the SP. Then inside the SP we will parse this VARCHAR CSV string and use those values in our SQL Queries:

-- As always I will use the AdventureWorks database šŸ™‚
USE [AdventureWorks2012]

-- Create an SP with NVARCHAR(MAX) parameter:
	@persons NVARCHAR(MAX)
	--SET @persons = 'Charles,Jade,Jim,Luke,Ken'

	SELECT T.C.value('.', 'NVARCHAR(100)') AS [Name]
	INTO #tblPersons
	FROM (SELECT CAST ('<Name>' + REPLACE(@persons, ',', '</Name><Name>') + '</Name>' AS XML) AS [Names]) AS A
	CROSS APPLY Names.nodes('/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

-- No execute this SP by passing a list of values comma separated as a single string:
EXEC uspGetPersonDetailsCSV 'Charles,Jade,Jim,Luke,Ken'
-- Check the output, objective achieved šŸ™‚

-- Final Cleanup


This is the most simplest and common method to pass multiple values to an SP, but not ideal. If there are multiple values with count ranging to thousands, then storing them in VARCHAR will not be possible and parsing them would be a big trade off.

So, to perform this operation we have another method by using XML String. Check this in my next [blog post].


Leave a Reply

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

WordPress.com Logo

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: