using sqlcommand with multiple ids

Question

So this works great:

select name from users where id = @id

However, that only selects 1 guy. Say I have 3 IDs instead, normally the SQL would look something like this (without using parameters)

select name from users where id in (4,6,9)

However, it doesn't seem to work when I write

select name from users where id in (@IDs)

and insert a list into @IDs, like this

cmd.Parameters.AddWithValue("@IDs", userIDs);

Is there any way to do what I'm trying to? It's important to note that the sql I'm calling is (and has to be) a stored procedure.

You can use a table value parameter (http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx) — meziantou, Nov 27 '13 at 17:51
Then what @Hejner said. You can see an example in an answer to this question: http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause — AllenG, Nov 27 '13 at 17:53
Create a temporary table, insert the IDs and join it to the table with the IDs. With large amounts of IDs you will hit limits. — Jon Raynor, Nov 27 '13 at 18:06

outis nihil · Accepted Answer · 2013-11-27T18:05:05.550

2

There are two ways to do this. The first is by passing a string to a stored procedure and then adding it to a dynamic query:

-- @IDs  = '4,6,9'
DECLARE @MyQuery nvarchar(max) 
SET @MyQuery = N'SELECT name FROM users WHERE id IN (' + @IDs + ')'
EXEC(@MyQuery)

On the other hand, if you are using SQL Server 2008 or later, you can use a table-valued parameter (this is my preference).

First, create a user-defined table type:

CREATE TYPE IDList AS TABLE (
  id int
)

THEN, use the user defined type as the type for your parameter:

DECLARE @IDs IDList 
INSERT INTO @IDs (ID) VALUES (4),(6),(9)
SELECT name FROM users u INNER JOIN @IDs i WHERE u.id = i.id

If you are using .NET to consume a stored procedure, you can find sample code for user-defined SQL types on MSDN.

edited Nov 27 '13 at 18:05

answered Nov 27 '13 at 17:59

outis nihil

if you are passing '4,6,9' wouldn't that not work since it needs to look like IN ('4','6','9') – Tsukasa Nov 27 '13 at 18:00
Tsukasa - If the data type of "ID" is string, then you would want ('4', '6', '9'); but if you look at his original question, it's pretty clear the data type is int or some other numeric type, so no. – outis nihil Nov 27 '13 at 18:02
It seems like the execute query is the way to go then, sadly my boss haven't updated our sql server. It just has so many downsides that a "normal" query doesn't have. – Hejner Nov 27 '13 at 18:07

score 0 · Answer 2 · answered Nov 27 '13 at 17:54

You can create a dynamic SQL query inside your stored procedure:

DECLARE @SQLQuery AS NVARCHAR(500)

SET @SQLQuery = 'select name from users where id in ( ' + @userIDs + ')'

EXECUTE(@SQLQuery)

You'll have to be careful and sanitize the contents of @userIDs though to prevent SQL injection attacks.

score 0 · Answer 3 · edited May 23 '17 at 10:31

0

On our side we are using iBatis.Net to manage this. Execute query sounds quite ugly but it still does the trick.

We were mostly using string split in SQL. See [question]How do I split a string so I can access item x?

edited May 23 '17 at 10:31

Community

answered Nov 27 '13 at 18:00

dolomitt

3 Answers3