reporting services - SSRS multi-value parameter using a stored procedure has too many values -

i working on ssrs report uses stored procedure containing few parameters.

1 of paramaters having problems because have option of selecting more 1 item (guid values) , exceeding 4000+ comma separated values.

store procedure: proc_getcompanyrecord

========================================

create procedure [dbo].[proc_getcompanyrecord]     (     @year varchar(max) = null,     @corecompanyid varchar(max) = null,     @companyid varchar(max) = null,     @ownerid varchar(max) = null     )  --the rest of query here pulls of needed columns  corecompany.corecompanyid in (select * ufnsplit (@corecompanyid, ',')) , company.companyid in (select * ufnsplit (@companyid, ',')) 

========================================

function: [dbo].[ufnsplit]

create function [dbo].[ufnsplit] (     @repparam varchar(max),     @delim char(1) ) returns @values table (item uniqueidentifier) begin   declare @chrind int   declare @piece varchar(max)   select @chrind = 1    while @chrind > 0     begin       select @chrind = charindex(@delim,@repparam)       if @chrind  > 0         select @piece = left(@repparam,@chrind - 1)       else         select @piece = @repparam       insert  @values(item) values(@piece)       select @repparam = right(@repparam,len(@repparam) - @chrind)       if len(@repparam) = 0 break     end   return   end 

when try run stored procedure providing 4000+ values @companyid parameter, works fine , return possible records.

but after deployed report in crm, run , click on view report shows following error, "an error has occurred"

i diagnosed , found 4000+ guid comma saperated values store procedure , in statement:

msg 8152, level 16, state 10, line 7 string or binary data truncated.

please help!

when make @companyid parameter query. add option 'select all'.

select 'select all' companyid, 1 myorder  union  select cast(companyid varchar), 2 myorder  companytable  order myorder 

with user doesn't have select 4000 values can select 'select all' option , select them. , update proc_getcompanyrecord handle new 'select all' value of companyid.

create procedure [dbo].[proc_getcompanyrecord]     (     @year varchar(max) = null,     @corecompanyid varchar(max) = null,     @companyid varchar(max) = null,     @ownerid varchar(max) = null     )  --the rest of query here pulls of needed columns  corecompany.corecompanyid in (select * ufnsplit (@corecompanyid, ',')) , (company.companyid in (select * ufnsplit (@companyid, ',')) or company.companyid = 'select all')