SQL SELECT Statement - Multiple IDs in IN Clause

Question

Possible Duplicate:
Parameterizing an SQL IN clause?

Every now and then I work on a system that allows the user to select multiple items and then perform a bulk action on them. Typically, I resorted to building the SQL at runtime, something like this:

string inClause = String.Join(", ", selectedIds);
string command = "SELECT * FROM Customer WHERE CustomerId IN ({0})";
command = String.Format(command, inClause);

Of course, this style of code is insecure because of SQL injection. I could solve that by putting in parameter placeholders and creating parameters.

Still, I am wondering if there is another approach that I've just not considered. I certainly don't want to execute the command once for each ID.

Take a look at http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause — Alex K., Jan 07 '13 at 16:12
If `selectedIds` is an array of integers, there's no danger of SQL injection. There's no way to stuff an `'` in an integer. — Andomar, Jan 07 '13 at 16:12

score 1 · Answer 1 · answered Jan 07 '13 at 16:10

1

There are two good approaches:

Build the string with command placeholders (like you said)
Join to the values of a TVP

Burning the IDs into the SQL is not good because it prevents plan caching and opens the potential for injection.

answered Jan 07 '13 at 16:10

usr

168,620
35
240
369

score 0 · Answer 2 · answered Jan 08 '13 at 01:21

You can build an XML string and then pass it to a stored proc. Executing it would look like:

EXECUTE getLocationTypes '<IDList><ID>1</ID><ID>3</ID></IDList>'

The stored proc would look something like:

create proc [dbo].[getLocationTypes](@locationIds XML)
as  
begin  
set nocount on  

SELECT locationId, typeId
FROM xrefLocationTypes 
WHERE locationId 
IN (SELECT Item.value('.', 'int' )
FROM @locationIDs.nodes('IDList/ID') AS x(Item))
ORDER BY 1, 2

end

Notice the data type of the parameter is XML. This is a little more complicated than what you are doing, guess you could do it all in a single SQL string.

SQL SELECT Statement - Multiple IDs in IN Clause

2 Answers2