Call stored procedure with table-valued parameter from java

In my application I want to execute query like SELECT * FROM tbl WHERE col IN (@list) 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

http://www.sommarskog.se/arrays-in-sql-2008.html

This link says to use table-valued parameter. So I created user-defined data type using Microsoft SQL Server Management Studio.

CREATE TYPE integer_list_tbltype AS TABLE (n int NOT NULL PRIMARY KEY)

Then I wrote stored procedure

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)

and then using management studio only I executed this procedure

DECLARE @mylist integer_list_tbltype
INSERT @mylist(n) VALUES(9),(12),(27),(37)
EXEC get_product_names @mylist

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

CallableStatement proc_stmt = null;
proc_stmt = con.prepareCall("{call test(?)}");
proc_stmt.setString(1,someValue);

but how to call stored procedure in table-value parameter case?

Solution 1:

This is documented here in the JDBC driver manual. In your case, you'd have to do this:

try (SQLServerCallableStatement stmt =
    (SQLServerCallableStatement) con.prepareCall("{call test(?)}")) {

    SQLServerDataTable table = new SQLServerDataTable();   
    sourceDataTable.addColumnMetadata("n", java.sql.Types.INTEGER);   

    sourceDataTable.addRow(9);
    sourceDataTable.addRow(12);
    sourceDataTable.addRow(27);
    sourceDataTable.addRow(37);

    stmt.setStructured(1, "dbo.integer_list_tbltype", table);  
}

I've also recently documented this in an article.

Solution 2:

Looks like this is a planned addition to JDBC but has not been implemented yet:

http://blogs.msdn.com/b/jdbcteam/archive/2012/04/03/how-would-you-use-table-valued-parameters-tvp.aspx

Pass the parameter as a delimited string ("9,12,27,37") and then create a table-valued function in SQL Server called "fnSplit" or whatever that will return the integer values in a table (just search for "sql server split function," there are millions of them).