Forum Discussion

Seanan's avatar
Seanan
Solution Supplier
2 years ago

Error when passing multiple values to a parameter in report builder

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...
report builder
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

  • Seanan's avatar
    Seanan
    Solution Supplier
    2 years ago

    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.