Menu
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.
901
The stored procedure with multiple parameters can be created by using the parameter names separated by a comma. Each parameter's data type can be defined along with its name as shown in the example below. As we used LIKE operator, so the query searched any names starting with letter 'J'.
Sql Injection. Build a parameterised query Select * from MyTable Where field = @p1 [Or field = @p2] using the count of values in the list, then assign the values to the parameters as a safer option.
As a program, a stored procedure can take parameters. There are three types of parameters: IN, OUT and INOUT.
In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to convert the string parameter to xml variable and split it using comma delimiter and then you can query it.
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>'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
