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
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] GO -- Create an SP with NVARCHAR(MAX) parameter: CREATE PROCEDURE uspGetPersonDetailsCSV ( @persons NVARCHAR(MAX) ) AS BEGIN --DECLARE @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 END GO -- No execute this SP by passing a list of values comma separated as a single string: EXEC uspGetPersonDetailsCSV 'Charles,Jade,Jim,Luke,Ken' GO -- Check the output, objective achieved š -- Final Cleanup DROP PROCEDURE uspGetPersonDetailsCSV GO
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].
Comments (0)
Trackbacks (4)
Leave a comment
Trackback
-
September 9, 2012 at 6:32 pmPassing multiple/dynamic values to Stored Procedures & Functions | Part2 – by passing XML « SQL with Manoj
-
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 6:08 pmPassing multiple values with a single Parameter in a Stored Procedure – SQL Server | SQL with Manoj