How to pass the (java.util.Collection ) parameter to MS SQL Stored Procedure


A parameter has declared as Collection in jasper report and need to pass this parameter to MS SQL stored procedure.

in MS SQL stored procedure that variable is declared as varchar(100).

any idea?

eagleeye's picture
Joined: Aug 24 2009 - 1:29am
Last seen: 3 years 9 months ago

2 Answers:


You may have to write a query executer that extends the basic JdbcQueryExecuter functionality for the SQL query language. I remember in JRS 3.5.x we had implemented that but I am not sure if it is still necessary for the latest version of JRS since there seems to be better support for parameters containing a list of values (ie. the $X{} parameter replacement).

hittle's picture
Joined: Jan 18 2013 - 9:44pm
Last seen: 3 years 7 months ago

This is working for reports, but not if you want to make a topic of the report:

Input control:

Name: inp_abc
Class: java.util.Collection
Default Value: new ArrayList()

String list variable (parameter)

Name: var_abc
Class: java.lang.String
Default Value: $P{var_abc}.join(",",$P{inp_abc} )

Then pass the var_abc to the proc.

in the proc, you can use the following syntax to get the result:

select * from table

where ','+@var_abc+',' like '%,'+TableColumn+',%';

Balder's picture
Joined: Nov 21 2015 - 11:34am
Last seen: 1 month 5 days ago