How to write a stored procedure that contains several parameters for a request?

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 can I pass 1,2,3 as parameters, and these identifiers can be concise.

Answer 1

You can send your identifier as XML to 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 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>' 

Answer 2

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

 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 for input '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 shown below:

 Create Proc dbo.GetApplicantsByIDs @Ids NVARCHAR(MAX) as Select * from Applicants where ID in (SELECT value FROM [dbo].[Split](',', @Ids) 

Answer 3

Another solution using table variables (for its sake):

 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 

Issued by: 1 David 2 John 4 Anna