dimanche 19 juin 2016

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?

Aucun commentaire:

Enregistrer un commentaire