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.

Table-Values or a String+Split come to mind; there be many duplicates found after a look. You'll want to alter the `IN` to a `JOIN` is most cases. — , Mar 17 '12 at 06:14
This is functionally identical to this question: [array parameter in T-SQL](http://stackoverflow.com/questions/1070095/array-parameter-tsql) - I would suggest reading the top rated answer there. — , Mar 17 '12 at 06:14

score 3 · Accepted Answer · answered Mar 17 '12 at 06:27

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>'

score 2 · Answer 2 · answered Mar 17 '12 at 15:31

I think there is a better solution. You can create a function like:

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, [stop]) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, [stop] + 1, CHARINDEX(@sep, @s, [stop] + 1)
      FROM Pieces
      WHERE [stop] > 0
    )
    SELECT pn as [index],
      SUBSTRING(@s, start, CASE WHEN [stop] > 0 THEN [stop]-start ELSE 8000 END) AS value
    FROM Pieces
  )

Then you can get the result from for input data '20,10,15,18,19'

SELECT * FROM [dbo].[Split](',', '20,10,15,18,19')

The result will be:

index   value
1   20
2   10
3   15
4   18
5   19

And I can rewrite your procedure as below:

Create Proc dbo.GetApplicantsByIDs         
         @Ids NVARCHAR(MAX)
as         

Select * from Applicants where ID in 
(SELECT value FROM [dbo].[Split](',', @Ids)

score 1 · Answer 3 · answered Mar 18 '12 at 13:22

Another solution using table variables (for the sake of it):

if exists (select table_name from information_schema.tables where table_name = 'Applicants')
    drop table Applicants
go

create table Applicants (
    Id int identity,
    Name varchar(50)
)
go

insert Applicants (Name) values ('David')
insert Applicants (Name) values ('John')
insert Applicants (Name) values ('Scott')
insert Applicants (Name) values ('Anna')
insert Applicants (Name) values ('Esther')
go

create type IDs as table (
    ID int
)
go

if exists (select routine_name from information_schema.routines where routine_name = 'GetApplicantsByIDs')
    drop proc GetApplicantsByIDs
go

create proc GetApplicantsByIDs (
    @IDs IDs readonly
)
as 
begin
    select * from Applicants A where Id in (select * from @IDs)
end
go


declare @MyIDs as IDs
insert @MyIDs values (2)
insert @MyIDs values (4)
insert @MyIDs values (1)

exec GetApplicantsByIDs @MyIDs
go

Produces: 1 David 2 John 4 Anna

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

3 Answers3