Pass multiple parameters to an SQL query

Question

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

65568752877
86924355382
55317961378
82331493146

I would like to pass them as a parameter in a consultation SQL, I’m trying this way:

Snippet

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

And then

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

But make that mistake:

com.microsoft.sqlserver.jdbc.Sqlserverexception: Error Converting data type varchar to bigint.

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

When I spin it with the values that way it works:

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

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

Answer 1

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

"65568752877, 86924355382, 55317961378, 82331493146"

This value cannot be converted to BIGINT, since it is a String. The correct is to do exactly the way it works, that is to say:

(65568752877, 86924355382, 55317961378, 82331493146)

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

Its function fetch, therefore, it must receive the string "65568752877,86924355382, 55317961378,82331493146" and, internally, treat it, to pass to SQL correctly.

Update - Code to turn 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 montaClausulaInSQL receives a String separated by "," and returns an IN clause to be used in SQL. If the value of the field to be made the Query is numerical, true must be passed in the 2nd parameter. In this case, the apostrophe will not be placed on each IN element.