Passing Multiple Parameters to SQL Server [duplicate]

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, by Erland Sommarskog. — Oded
– Oded, Commented Dec 27, 2012 at 13:57
This question has been asked before; look at [the answer here][1] [1]: stackoverflow.com/a/1033208/96505 — Simon Martin
– Simon Martin, Commented Dec 27, 2012 at 14:00
@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
– Oded, Commented Dec 27, 2012 at 14:03

Oded · Accepted Answer · 2012-12-27 14:01:46Z

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, 2012 at 14:01

Oded

501k102 gold badges900 silver badges1k bronze badges

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

Comments

JOpuckman · Accepted Answer · 2012-12-27 17:22:50Z

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, 2012 at 17:22

JOpuckman

1,3869 silver badges18 bronze badges

1 Comment

Tony Hopkinson Over a year ago

Nice, I'll remember that one.

Atheer Mostafa · Accepted Answer · 2012-12-27 15:56:13Z

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, 2012 at 15:56

Atheer Mostafa

7353 silver badges8 bronze badges

3 Comments

Tony Hopkinson Over a year ago

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.

Atheer Mostafa Over a year ago

@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.

Tony Hopkinson Over a year ago

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.

Collectives™ on Stack Overflow

Passing Multiple Parameters to SQL Server [duplicate]

3 Answers 3

Comments

1 Comment

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Linked

Related