pranay godha pranay godha - 5 months ago 217
SQL Question

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?

Answer

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'");