How to write a stored procedure which contains multiple parameters for in query?

Question

I want to write a stored procedure like this

Create Proc dbo.GetApplicantsByIDs

as

Select * from Applicants where ID in (1,2,3,4)

How i can pass 1,2,3 as parameters and these ids may be multiple.

Answer 1

You can send your id's as XML to the SP.

create procedure dbo.GetApplicantsByIDs
  @IDList xml
as

-- Table to hold the id's
declare @IDs table(ID int primary key)

-- Fill table with id's  
insert into @IDs(ID)
select X.ID.value('.', 'int')
from @IDList.nodes('/i') as X(ID)

select * 
from Applicants 
where ID in (select ID 
             from @IDs)

The parameter string should look like this:

'<i>1</i><i>2</i><i>3</i>'