CODEWITHSUNDEEP
c#sql-servert-sqlsqlcommandsqlparameter
Hejner
Hejner

Reputation: 392

using sqlcommand with multiple ids

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.

Upvotes: 0

Views: 1848

Answers (3)

outis nihil
outis nihil

Reputation: 736

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.

Upvotes: 2

dolomitt
dolomitt

Reputation: 281

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?

Upvotes: 0

System Down
System Down

Reputation: 6260

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.

Upvotes: 0

Related Questions