Search code examples
sqlreporting-servicessqldatatypesreportparameter

Multi-value parameter truncation

On my SSRS report I have a multi-value parameter which contains 250+ uniqueidentifier data type values. This works fine with a small selection of values in the parameter dropdown, but when user chooses (select all), they get this error:

An error occurred during local report processing.
String or binary data would be truncated.

Each uniqueidentifier field is 36 characters long, which means 250 of them added together result in a 9000 character string. This is what causes the truncation to occur.

What approach should I take to handle this situation?

Edit:

Couple snapshots of the stored procedure:

ALTER PROCEDURE [dbo].[spReport]
     @StartDate as datetime
    ,@EndDate as datetime
    ,@LocationId uniqueidentifier
    ,@UserIds uniqueidentifier

@UserIds is the multi-value parameter. It is used in the where clause of the query:

WHERE (U.UserId IN (@UserIds) OR @UserIds IS NULL)
Solution

  • You can't use an SSRS multi-value parameter with a stored procedure like that. You'll need to join the values in the report, pass them as a varchar(max), and then split them in the stored procedure:
    https://stackoverflow.com/a/9862901/124386
    http://www.codeulike.com/2012/03/ssrs-multi-value-parameters-with-less.html