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 (msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx) — meziantou
– meziantou, Commented Nov 27, 2013 at 17:51
Then what @Hejner said. You can see an example in an answer to this question: stackoverflow.com/questions/337704/… — AllenG
– AllenG, Commented Nov 27, 2013 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
– Jon Raynor, Commented Nov 27, 2013 at 18:06

outis nihil · Accepted Answer · 2013-11-27 18:05:05Z

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, 2013 at 18:05

answered Nov 27, 2013 at 17:59

outis nihil

7366 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tsukasa Over a year ago

if you are passing '4,6,9' wouldn't that not work since it needs to look like IN ('4','6','9')

outis nihil Over a year ago

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.

Hejner Over a year ago

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.

System Down · Accepted Answer · 2013-11-27 17:54:23Z

0

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.

answered Nov 27, 2013 at 17:54

System Down

6,2711 gold badge33 silver badges34 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:31:44Z

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, 2017 at 10:31

CommunityBot

11 silver badge

answered Nov 27, 2013 at 18:00

dolomitt

2913 silver badges5 bronze badges

Collectives™ on Stack Overflow

using sqlcommand with multiple ids

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related