SQL: Possible to use input array in where clause? [duplicate]

Question

Possible Duplicate:
Parameterizing an SQL IN clause?

Suppose I had a table of schools which school_name, school_enrolment

As input to my program, someone types a list of schools that they would like to see the enrolment for. Instead of generating an sql query like:

SELECT * FROM school_table
WHERE
school_name = 'program_input_1' or school_name = 'program_input_2' or school_name = 'program_input_3'

is it possible or straightforward to do something like

SELECT * from school_table
WHERE
school name in [array of program inputs]

as a much cleaner way of writing this?

Mark Byers · Accepted Answer · 2012-11-16 21:40:37Z

11

Yes, this is what IN is for:

SELECT col1, col2, ..., coln
FROM school_table
WHERE school_name IN ('program_input_1', 'program_input_2', 'program_input_3')

answered Nov 16, 2012 at 21:40

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

Comments

Leigh · Accepted Answer · 2012-11-16 22:20:44Z

2

You can use the IN(...) clause:

WHERE School_name in ('school 1','school 2')

In terms of passing an array, it depends on the programming language you are using to generate the SQL.

You would have to write a custom function that loops over the array, dynamically generating the IN clause

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

answered Nov 16, 2012 at 21:43

Lock

5,53217 gold badges71 silver badges116 bronze badges