Call stored procedure with table-valued parameter from java

Question

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?

Answer 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.