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.

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

Answer 3

The typical answers (comma delimited or XML) all have problems with SQL Injection. I needed an answer that allows me to use a PreparedStatement. So I came up with this:

StringBuilder query = new StringBuilder();
query.append(
  "DECLARE @mylist integer_list_tbltype;" +
  "INSERT @mylist(n) VALUES(?)");
for (int i = 0; i < values.size() - 1; ++i) {
  query.append(",(?) ");
}
query.append("; EXEC get_product_names @mylist ");
PreparedStatement preparedStmt = conn.prepareStatement(query.toString());
for (int i = 0; i < values.size(); ++i) {
    preparedStmt.setObject(i + 1, itemLookupValues.get(i));
}

Answer 4

Now it's been added to JDBC Driver 6.0. It CTP2 yet.

"This new driver now supports Table-Valued Parameters and Azure Active Directory. In addition to these new features, we have added additionally functionality for Always Encrypted. The driver also supports Internationalized Domain Names and Parameterized."

https://blogs.msdn.microsoft.com/jdbcteam/2016/04/04/get-the-new-microsoft-jdbc-driver-6-0-preview/

Download link:https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

Here is the documentation how to use it https://msdn.microsoft.com/en-us/library/mt651781(v=sql.110).aspx

Answer 5

After searching for a while I found answer of this problem.Specially when you use IN clause and no of operands are variable then you can use comma-delimited values as an input in IN clause.

Here's the example how the stored procedure that will retrieve all lawyers of the given lawyer type in the provided ZIP code will look like using dynamic SQL.

CREATE PROCEDURE [dbo].[GetLawyers] ( @ZIP CHAR(5), @LawyerTypeIDs VARCHAR(100) )
AS

DECLARE @SQL     VARCHAR(2000)

SET @SQL = 'SELECT * FROM [dbo].[Lawyers]
            WHERE [ZIP] = ' + @ZIP + ' AND
                  [LawyerTypeID] IN (' + @LawyerTypeIDs + ')'
EXECUTE (@SQL)

GO

To execute the stored procedure passing the ZIP code entered by the user and the selected lawyer types in a comma separated value:

EXECUTE [dbo].[GetLawyers] '12345', '1,4'

So conclusion is you do not need to use TVP. Whichever language[Java, PHP] you use just pass parameters as comma-delimited string to stored procedure and it will work perfect.

So in JAVA you can call above stored procedure:-

proc_stmt = con.prepareCall("{call GetLawyers(?,?)}");
proc_stmt.setString(1,"12345");
proc_stmt.setString(2,"'1,4'");