SQL Server IN with multiple values as a parameter

Question

I have a SP as below (example only) and want to filter the City based on values passed in as a parameter.

SELECT * FROM Customers
WHERE City IN (@cities_as_a_parameter);

In my C# code, I have the below

cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "MY_SPNAME";
cmd.Parameters.AddWithValue("@cities_as_a_parameter", "'Paris','London', 'xxx', 'yyyy'");

My question is how to pass multiple values to the SP parameter @cities_as_a_parameter ? Above code does not work and want to know what is the correct way of doing this.

What is the best way to do this ?

OR is there any other way to do this (without using multiple where conditions)?

You can't pass multiple values via a single parameter. You either need multiple parameters or else your sproc needs to accept the input as a single string and then it must split it into multiple values. — jmcilhinney, May 18 '15 at 02:11
Look at one of those duplicate questions: http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values, http://stackoverflow.com/questions/617706/passing-an-in-list-via-stored-procedure, http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure, http://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure. A real analysis of this topic is done on an excellent article about this: http://www.sommarskog.se/arrays-in-sql.html — Alejandro, May 18 '15 at 02:28

score 0 · Answer 1 · answered May 18 '15 at 02:19

0

Try this,

DECLARE @SQL     VARCHAR(3000)

SET @SQL = 'SELECT * 
            FROM Customers
            WHERE City IN (' + @cities_as_a_parameter +')';
EXECUTE (@SQL)

Just make sure you properly build your string.

answered May 18 '15 at 02:19

Anonymous Duck

2,942
1
12
35

Vulnerable to SQL injection, insanely slow as the list grows and input is restricted in size. Works, but it's the worst possible method, unfortunately. – Alejandro May 18 '15 at 02:24
yah, the only method i can think :P, but you can improve it, need your inputs. – Anonymous Duck May 18 '15 at 02:25
Look at my comment in the question for some alternatives. Too long and complex to describe myself :D, but there are good links there. – Alejandro May 18 '15 at 02:32

score -1 · Answer 2 · answered May 18 '15 at 02:23

-1

put the parameter after your stored proc name

CREATE PROCEDURE [dbo].[your_sp_name](@cities_as_a_parameter navarchar(10))

SELECT * FROM Customers

WHERE City IN (@cities_as_a_parameter);

answered May 18 '15 at 02:23

odlan yer

721
1
7
15

SQL Server IN with multiple values as a parameter

2 Answers2