0

here is my situation:

I am able to call a SQL SERVER stored procedure from a JAVA SERVLET (the servlet handles the data received by an http request, creates the sql statement, connect to DB and execute the statement), Everithing was good with any of basic values formats (Datetime,varchar,int ecc ecc) but now i have to call a stored procedure that accept a "temporary" TABLE VARIABLE. this table has to be fullfilled with the values of an Array of JSON OBJECT. How can i create this temporary TABLE VARIABLE??

suppose my JSONARRAY to be:

[
  {
   "name" : "gio",
   "surname : "venice",
   "age":"28 years"
  }, 
  {
   "name" : "frek",
   "surname : "joshua",
   "age":"21 years"
  },
  .....
]

i would like to have a table variable with these 3 columns(name,surname,age) and as much rows as the JSONARRAY length AND that variable has to be passed as aparameter like this:

String variable1 = "blabla";
Int variable2 = 3;
TableVariable tablevariable = "tablevariable_that_i_donno_how_to_build"
String query = "{call storedprocedureblablalba(?,?,?)}";
    CallableStatement cstmt = connection.prepareCall(query);  
    cstmt.setString(1,variable1);
    cstmt.setString(2,variable2);
    cstmt.setString(3,tablevariable );

Thanks for any kind of help!!!

Gio Venice
  • 55
  • 8
  • Do you actually have the table type created in your database? Create your table type, declare your table type in your procedure (ex: DECLARE @TableTypeVariable AS YourTableType) and then select from your table type variable after you insert your rows into the table type. – Jacob H May 10 '17 at 12:54
  • I'm not sure it's possible that way. Why don't you call the procedure once per element, e.g. in a batch, or use a real temporary table? – Thomas May 10 '17 at 12:54
  • my stored procedure accept a tablevariable as parameter (of course this table variable has to be of a specific table variable type) the servlet in fact receive a JSON ARRAY that is similar to the type of the table that the stored procedure accept – Gio Venice May 10 '17 at 12:59

0 Answers0