In my earlier article, I developed a solution How to use multiple values for IN clause using same parameter (SQL Server). Recently, I received a query, inquiring how to pass multiple values through one parameter in a stored procedure.
Given below are the two different Methods :
Method 1 : Using XQuery
In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to convert the string parameter to xml variable and split it using comma delimiter and then you can query it.
Given below is the script.
--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
CREATE PROCEDURE usp_Employeelist
@Cities NVARCHAR(30)
AS
DECLARE @CitiesXML AS XML
SET @CitiesXML = cast(('<a>'+replace(@Cities,',' ,'</a><a>')
+'</a>') AS XML)
SELECT
BusinessEntityID
, FirstName
, JobTitle
, City
FROM HumanResources.vEmployee
WHERE City IN
(
SELECT
A.value('.', 'varchar(max)')
FROM @CitiesXML.nodes('A') AS FN(A)
)
ORDER BY BusinessEntityID
GO
--Execute the stored procedure using multiple values
--through one parameter in this stored procedure
USE AdventureWorks2012
GO
EXEC usp_Employeelist
'Cambridge,Newport Hills,Berlin,Bordeaux'
GO
--OUTPUT
Method 2 : Using Dynamic queryhe
In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to replace the parameter with single quote and create a dynamic query and execute it.
Given below is the script.
--This script is compatible with SQL Server 2005 and above. USE AdventureWorks2012 GO CREATE PROCEDURE usp_Employeelist @Cities NVARCHAR(30) AS DECLARE @Sql VARCHAR(MAX) SET @Cities = REPLACE(@Cities,',',''',''') SET @Sql = 'SELECT BusinessEntityID , FirstName , JobTitle , City FROM HumanResources.vEmployee WHERE City IN ( ''' + @Cities + ''' ) ORDER BY BusinessEntityID' EXEC (@Sql) GO --Execute the stored procedure using multiple values --through one parameter in this stored procedure USE AdventureWorks2012 GO EXEC usp_Employeelist 'Cambridge,Newport Hills,Berlin,Bordeaux' GO --OUTPUT
