How do you write SQL for PreparedStatement using the WHERE x IN clause?

Question

I have a query that looks like this:

SELECT last_name,
       first_name,
       middle_initial
FROM   names
WHERE  last_name IN ('smith', 'jones', 'brown')

I need to be able to parameterize a list in an IN clause to write it as a JDBC PreparedStatement. This list can contain any number of names in it.

The correct way to do this is:

SELECT last_name,
       first_name,
       middle_initial
FROM   names
WHERE  last_name IN (?)

and then build a list of options? Or is there a better (more correct) way to do this?

Answer 1

In short, you cannot out of the box. However, with Spring, you can do what you want. See How to create a dynamic "in (...)" sql list through Spring JdbcTemplate?

Answer 2

SQL IN - SQL, SQL-, , .

Answer 3

. 100%. , - .

, , String -, :

Session s = getSession();
Criteria crit = s.createCriteria(this.getOrderListingClass());
crit.add(Expression.sql(String.format("{alias}.orderId in (%s)", orderIds)));
crit.add(Expression.eq("status", OrderInfo.Order_STATUS_UNFILLED));
orders = crit.list();

orderId "SELECT x FROM y WHERE IN (% s)".

• orderIds String , - ..

• - , , SQL . , , - 2000+ ( MS SQL). - , .

, kludgy... Ids Where-clause, , . , Ids .

Answer 4

, varchar. , sql-:

  CREATE PROCEDURE dbo.[procedure_name]

        @IN_LIST VARCHAR(MAX)
    AS
    BEGIN

        DECLARE @SQL VARCHAR(MAX)
        SET @SQL = '
            SELECT last_name,
                first_name,
                middle_initial
            FROM names
            WHERE  last_name IN (' + @IN_LIST + ')'

        EXECUTE(@SQL)
    END

Just make sure your @IN_LIST is formatted as a string containing single quotes and commas. For example, in java:

String inList = "'smith','jones','brown'";

Answer 5

If you are using MS SQL Server, try changing your TSQL to use UDF, maybe this my post can help you