Parameter issue when using a stored procedure data set

ttessitore

I am attempting to use a sql server stored procedure in my data set. This is my first attempt at using a stored procedure. The stored procedure I am using has 5 input parameters. They are:

create procedure [dbo].[getUnitOnOffDuty] (@startDate varchar(100),@endDate varchar(100),@tenantId varchar(255),
@unitOrg varchar(500),@unitName varchar(255))

I want to populate these values using report parameters.

I created the dataset and this is my queryset

call dbo.getUnitOnOffDuty(?,?,?,?,?);

These seems to work fine. When I got the parameters panel, I attempt to use the link to report parameter. It appears to work fine until I hit ok after I have linked all of them. I get the following error:

A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.6.2.r262_v20110215
Error Code:data.engine.fail.prepareExecution
Error Message:Failed to prepare the query execution for the data set: UnitOnOffDuty
Cannot set the string value (33-34) to parameter 5.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Invalid parameter index 5.
;
java.sql.SQLException: Invalid parameter index 5.

Any ideas? I have made sure that all the datatypes are in sync and I have put default values on all of the report parameters. The 33-34 is the default value of unitname report parameter.

Any help would be greatly appreciated. I will attach the stored procedure and the rptdesign file.

Tubal

My guess is that your function call is seeing parameter 5 as an integer (33-34 = -1).

You could try converting it to a string in your sql,

something like: call dbo.getUnitOnOffDuty(?,?,?,?,convert(varchar(255), '?'));

or putting single quotes around the default value ('33-34').