Pass multiple parameters to an SQL query

Question

Pass multiple parameters to an SQL query

Navigation

#1 by (5 votes)

3

Let's say I have the following CPF's:

65568752877
86924355382
55317961378
82331493146

I would like to pass them as a parameter in a query SQL , I am trying as follows:

SQL Excerpt

... " AND Beneficiario.Codigo IN ('"+codigo+"') ");

And then

List<Cliente> lista = buscar(c, " 65568752877, 86924355382, 55317961378, 82331493146 ");

But of this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type varchar to bigint.

When I pass only one CPF it works, the problem is when I try to pass more than one.

When I start with values this way it works:

AND Beneficiario.Codigo IN
(65568752877, 86924355382, 55317961378, 82331493146)

NOTE: The codes (CPF) are not fixed, they can change and the number of codes passed by parameters can also change.

sql java sql-server

asked by anonymous 07.08.2015 / 14:15

1 answer

How to simplify a foreach by a Linq - Lambda expression How to know the line number where the ID was displayed

score 5 · Accepted Answer

This is occurring because your code field is BIGINT and you are trying to pass this value to it:

"65568752877, 86924355382, 55317961378, 82331493146"

This value can not be converted to BIGINT since it is a String. The correct thing is to do exactly the way it works, ie:

(65568752877, 86924355382, 55317961378, 82331493146)

The above clause specifies that you want records where the code is 65568752877 or 86924355382 or 55317961378 or 55317961378

Your search function should therefore receive the string "65568752877,86924355382, 55317961378,82331493146" and internally treat it to pass SQL correctly.

Update - Code to transform a String into an IN clause:

public static void main(String[] args) {
    System.out.println(montaClausulaInSQL("12222,2233,2232",true));
}

public static String montaClausulaInSQL(String valor, Boolean valorNumerico) {
    String[] vetor = valor.split(",");

    String clausulaIn = "(";

    for (int i = 0; i < vetor.length; i++) {
        clausulaIn = clausulaIn + (!valorNumerico?"'":"") + vetor[i] + (!valorNumerico?"'":"") + ",";
    }

    clausulaIn = clausulaIn.substring(0,clausulaIn.length()-1);
    clausulaIn = clausulaIn + ")";

    return clausulaIn;
}

The function mountsInSQL receives a separate String by "," and returns an IN clause to be used in SQL. If the value of the field to be made Query is numeric, one must pass true in the 2nd parameter. In this case, the apostrophe will not be placed in each IN element.