title: SQL Server 2008 Passing Multi-value Parameters or Parameter Array to a Stored Procedure
body: <p>I have two tables tbl_Properties and tbl_Locations</p>
  
  <p>tbl_Properties has list of properties (including FK Location_Id)
  tbl_Location has a list of locations</p>
  
  <p>I am using following stored procedure to search properties in a location:</p>
  
  <pre><code>spPropGetSearch
  
  (
  @Location_Id int
  )
  
  AS
  
  SELECT
  P.Prop_Id,
  P.Prop_Title,
  P.Prop_Bedrooms,
  P.Prop_Price,
  L.Location_Title
  
  FROM 
  tbl_Properties P
  INNER JOIN tbl_Locations L ON L.Location_Id = P.Location_Id
  
  WHERE
  (P.Location_Id = @Location_Id OR @Location_Id = '0')
  ORDER BY P.Prop_DateAdded DESC
  </code></pre>
  
  <p>I pass location_id (such as '1002') and it works just fine and returns properties located within that location / area.
  Now, I want to pass multiple location_Ids such as '1002', '1005', '1010' to search properties located in all of those areas.</p>
  
  <p>How would I do that? 
  I will appreciate a detailed reply as I am not a database expert.</p>
  
  <p><strong>I found the following example and it's working fine. Please can you look into and check if there's any vulnerability</strong></p>
  
  <pre><code>USE AdventureWorks2012
  GO
  CREATE PROCEDURE usp_Employeelist
  @Cities NVARCHAR(30)
  AS
  DECLARE @CitiesXML AS XML
  SET @CitiesXML = cast(('&lt;a&gt;'+replace(@Cities,',' ,'&lt;/a&gt;&lt;a&gt;')+'&lt;/a&gt;') 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 
  </code></pre>