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?

Solution 1

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.

Solution 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, ',')
);

Comments

[Oded]

What version of SQL Server are you using? I suggest reading Arrays and Lists in SQL Server, by Erland Sommarskog.

[Oded]

Then Table-Valued Parameters are the way to go.

[Oded]

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

[Tony Hopkinson]

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]

Nice, I'll remember that one.

[Atheer Mostafa]

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

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.