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.
3 Answers
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>'
Comments
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)
answered Mar 17, 2012 at 15:31
Amir Pournasserian
1,6846 gold badges26 silver badges52 bronze badges
Comments
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
INto aJOINis most cases.