0

I'm attempting to pass a variable amount of numbers to a stored procedure to limit the output from it. Let's say I have 6 departments numbered 1, 2, 3, 4, 5, 6. I would like the user to input any variety of those numbers. So they might choose 1, 3, 6 or just 2, 4, or just 3, or whatever they want to see. Can that be passed like a variable into an IN statement in the WHERE clause of a stored procedure? If not, how else might one do this? I'd like to keep it as simple as possible. Thanks!

JFrizz
  • 123
  • 12
  • Placeholders can only represent SINGLE values. you can't pass in multiple values in a single placeholder, e.g. `WHERE foo IN (?)` and passing in `1,2,3` will be executed as if it was written `WHERE foo = '1,2,3'`. You can't use a "pre" prepared statement when you have arbitrary numbers of placeholders. You have to dynamically build your query string with as many placeholders as you have values, then pass them in with a 1:1 mapping. – Marc B Oct 03 '16 at 17:08
  • 1
    One option is to convert the comma separated string of numbers to a temp table, then join or otherwise use that temp table in your query. One way of doing it: http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S – Paul Abbott Oct 03 '16 at 17:13
  • 3
    What is your DBMS? – Serg Oct 03 '16 at 17:14
  • Possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – Andrew Morton Oct 03 '16 at 17:21
  • If you are using Postgres, pass an array and use `= any()` –  Oct 03 '16 at 17:50
  • Thanks for all the help so far. Using Microsoft SQL Server 2014. – JFrizz Oct 03 '16 at 18:04

1 Answers1

0

A universal and robust SQL way of passing an array of values to a stored procedure would require multiple statements.

  1. Insert your values into a (temporary) table.
  2. Call your stored procedure which should contain joining logic to this table.
  3. Remove recently added values from the table (if necessary)

You may want to enclose these statements into a transaction.

Zso
  • 476
  • 4
  • 6