topic Re: Error when passing multiple values to a parameter in report builder in Report Server

Error when passing multiple values to a parameter in report builder

Seanan — Thu, 01 Feb 2024 15:31:25 GMT

I have a report builder report that uses an Azure SQL database stored procedure for it's dataset.

In this report I have 2 parameter that are single value only and I now want to add a third parameter that allows multiple values to be passed to it.

I've setup my dataset properties -> parameters to include the new parameter value:

However when I enter my values I get the following error:

My stored procedure has a variable at the top:

@CandidateId int

and my where statement has the line:

AND CandidateId IN (@CandidateId)

Any ideas what may be causing this issue and how I can pass multiple values into this parameter?

Re: Error when passing multiple values to a parameter in report builder

Anonymous — Fri, 02 Feb 2024 01:36:26 GMT

Hi Seanan ,

According to your error message . It looks like CandidateId is an nvarchar datatype in the table and you've passed an int through your procedure, either use a forced conversion and convert @CandidateId to an nvarchar, or change the CandidateId datatype to an integer in the table.

DECLARE @CandidateID_coast NVARCHAR(15) SET @CandidateID_coast = CAST(@CandidateID AS NVARCHAR)

Then use @CandidateID_coast instead of @CandidateId in SELECT, INSERT and WHERE clause.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Error when passing multiple values to a parameter in report builder

Seanan — Fri, 02 Feb 2024 09:06:41 GMT

Hi Anonymous

Thanks for your swift response.

This is the part that is confusing me because in my source stored procedure the data type for CandidateId is an integer, as shown below:

I tried what you mentioned but it throws the same error because the where statements looks like this:

WHERE CandidateId IN (@CandidateId_coast)

But CandidateId is a integer and CandidateId_cost is an nvarchar.

I feel like the error comes the way multiple values are concatenated and then passed to the parameter, I've tried a few different expressions to break them up and pass them into the (@CandidateId) parameter but I haven't had any success.

Re: Error when passing multiple values to a parameter in report builder

josef78 — Sun, 04 Feb 2024 21:13:31 GMT

Try, do not use "Parameters" page in DataSet configuration (left this empty), and enter parameter name directly into SQL "Query" like this

AND CandidateId IN (@CandidateId)