CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005t-sql
Mujassir Nasir
Mujassir Nasir

Reputation: 1730

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

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.

Upvotes: 5

Views: 2885

Answers (3)

David Brabant
David Brabant

Reputation: 43559

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

Upvotes: 1

Amir Pournasserian
Amir Pournasserian

Reputation: 1674

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)

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 139000

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

Upvotes: 3

Related Questions