Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – Passing multiple values through one parameter in a stored procedure’

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

Passingmultiple1.1

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

Passingmultiple1.1

Advertisements

Read Full Post »