Passing Multiple Parameters to SQL Server

Question

Possible Duplicate:
Parameterizing an SQL IN clause?
Comma-separated value insertion In SQL Server 2005

I'm trying to search in my database using where in clause, but my string is in follow format:

'233052,57516351,254689'

I need to do an consult in my database using the following query:

SELECT * FROM myTable WHERE field IN (@list_string)

How I do to make this action?

What version of SQL Server are you using? I suggest reading [Arrays and Lists in SQL Server](http://www.sommarskog.se/arrays-in-sql.html), by Erland Sommarskog. — Oded, Dec 27 '12 at 13:57
Then [Table-Valued Parameters](http://msdn.microsoft.com/en-us/library/bb510489.aspx) are the way to go. — Oded, Dec 27 '12 at 14:00
This question has been asked before; look at [the answer here][1] [1]: http://stackoverflow.com/a/1033208/96505 — Simon Martin, Dec 27 '12 at 14:00
There 's a few ways to do this and the article mentioned by @Oded will help, but it's messy whichever way you look at it, personally I'd try for an other way to get the same result. — Tony Hopkinson, Dec 27 '12 at 14:03
@TonyHopkinson - For a variable list of values, you have to use one of these options. TVPs are probably the best option, when it comes to SQL Server. — Oded, Dec 27 '12 at 14:03
@SimonMartin - for putting links in comments, you have to use the in-line syntax, where you use `[]()`, placing text in the first, and the link in the second. — Damien_The_Unbeliever, Dec 27 '12 at 15:53
@Oded , I know they are out there but i was stuck on sql 2000 as a lowest common denominator for so long (dropped this year :( ) I tend to avoid this scenario by reflex now. and my lcd is now 2005 so I still can't use 'em apparently — Tony Hopkinson, Dec 28 '12 at 03:09
@TonyHopkinson - Fair enough. With 2000 you had to go with a delimited string or XML. — Oded, Dec 28 '12 at 08:50

score 5 · Answer 1 · answered Dec 27 '12 at 14:01

5

Use Table-valued parameters, introduced in SQL Server 2008.

These let you pass in a table structure that you can use to query on.

For other options, I suggest reading Arrays and Lists in SQL Server, by Erland Sommarskog.

answered Dec 27 '12 at 14:01

Oded

score 2 · Answer 2 · answered Dec 27 '12 at 17:22

2

I've been using Itai Goldstein's Split function for years for this very situation. You could then do the following:

SELECT * 
FROM [myTable] 
WHERE [field] IN (
    SELECT [Data]
    FROM [dbo].[Split] (@list_string, ',')
);

answered Dec 27 '12 at 17:22

JOpuckman

score 0 · Answer 3 · answered Dec 27 '12 at 15:56

0

Try EXEC of sql statement concatenation:

declare @sql varchar(200)
set @sql='SELECT * FROM myTable WHERE field IN ('+@list_string+')'
exec(@sql)

answered Dec 27 '12 at 15:56

Atheer Mostafa

1

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. – Tony Hopkinson Dec 28 '12 at 12:35
@Tony I agree if the list_string is client data, but if the list_string source list is safe and already validated, this will be the fastest execution and best performance query. – Atheer Mostafa Dec 28 '12 at 12:43
Or query takes longer to parse, because it's longer, doubt it's going to take longer to execute though. The comment was just a heads up for the many many peoples who keep perpetuating sql injection mistake. – Tony Hopkinson Dec 28 '12 at 18:46

3 Answers3