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

[eagleeye]

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?

[hittle]

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

[Balder]

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+',%';