title: Call stored procedure with table-valued parameter from java
body: <p>In my application I want to execute query like <strong>SELECT * FROM tbl WHERE col IN (@list)</strong> where,@list can have variable no of values. I am using MS SQL server database. When I google this problem then I found this link</p>
  
  <p><a href="http://www.sommarskog.se/arrays-in-sql-2008.html" rel="noreferrer">http://www.sommarskog.se/arrays-in-sql-2008.html</a></p>
  
  <p>This link says to use table-valued parameter. So I created user-defined data type using Microsoft SQL Server Management Studio.</p>
  
  <p><strong>CREATE TYPE integer_list_tbltype AS TABLE (n int NOT NULL PRIMARY KEY)</strong></p>
  
  <p>Then I wrote stored procedure </p>
  
  <pre><code>CREATE PROCEDURE get_product_names @prodids integer_list_tbltype READONLY AS
     SELECT p.ProductID, p.ProductName
     FROM   Northwind.dbo.Products p
     WHERE  p.ProductID IN (SELECT n FROM @prodids)
  </code></pre>
  
  <p>and then using management studio only I executed this procedure</p>
  
  <pre><code>DECLARE @mylist integer_list_tbltype
  INSERT @mylist(n) VALUES(9),(12),(27),(37)
  EXEC get_product_names @mylist
  </code></pre>
  
  <p>and it is giving me correct output. But I am wondering how to call this stored procedure from java source code. I know how to call simple stored procedure with constant number of argument</p>
  
  <pre><code>CallableStatement proc_stmt = null;
  proc_stmt = con.prepareCall("{call test(?)}");
  proc_stmt.setString(1,someValue);
  </code></pre>
  
  <p>but how to call stored procedure in table-value parameter case?</p>