0

I have to pass a list to a stored procedure which is selected by user. That list I have as a string at java end. Example: String list="123,467,980";

Now if I take this string to stored procedure as it is then how would it work.

I cannot use it as mentioned below:

select * from table_name where code in (list_var)

So can anyone guide me that what would be best approach for achieving this functionality.

Onki
  • 1,879
  • 6
  • 38
  • 58
  • Loook into using Table-Valued Parameters, that or you can use a string split function like the one here: http://www.4guysfromrolla.com/webtech/031004-1.shtml – Kevin Cook Jul 25 '14 at 13:38

1 Answers1

1

Compose your SQL at your client side:

String sql = "select * from table_name where code in (" + list_var + ")";

Or, If what you want is to call the stored procedure which actual parameters should be the ones represented in your list string:

String [] ids = list.split(",");
String sql = "{call yourProcedure(";
for(int i = 0; i < ids.length; i++)
    sql +=  (i == 0?"":",") + "?";
sql += ")}";
CallableStatement cst = con.prepareCall(sql);
for(int i = 0; i < ids.length; i++)
    cst.setInt(i+1, Integer.parseInt(ids[i]));

Alternatively:

String sql = "{call yourProcedure(" + list + ")}";
CallableStatement cst = con.prepareCall(sql);
Pablo Francisco Pérez Hidalgo
  • 27,044
  • 8
  • 36
  • 62
  • You have provided three answers. The second one is good. The first and third ones risk SQL injection. – Dawood ibn Kareem Jul 25 '14 at 19:14
  • @DavidWallace You are right although the OP does not specify whether the `list` is the result of gathering the raw input the user provides, which could result in hazardous SQL injection situations for the two answers you mention, or if it is composed by his/her program indirectly. Case, the latter, where SQL injection could not be possible. – Pablo Francisco Pérez Hidalgo Jul 28 '14 at 07:10