Q: SSRS multi-value parameter using a stored procedure has too many values I am working on a SSRS report that uses a stored procedure containing a few parameters.
1 of the paramaters I am having problems with is because I have the option of selecting more than one item (guid values) and they are exceeding 4000+ comma separated values.
Store Procedure: proc_GetCompanyRecord
========================================
CREATE PROCEDURE [dbo].[proc_GetCompanyRecord]
    (
    @Year AS VARCHAR(MAX) = NULL,
    @CoreCompanyId AS VARCHAR(MAX) = NULL,
    @CompanyId AS VARCHAR(MAX) = NULL,
    @OwnerId VARCHAR(MAX) = NULL
    )

--THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS

WHERE CoreCompany.CoreCompanyId in (SELECT * FROM ufnSplit (@CoreCompanyId, ','))
AND Company.CompanyId in (SELECT * FROM ufnSplit (@CompanyId, ','))

========================================
Function: [dbo].[ufnSplit]
CREATE FUNCTION [dbo].[ufnSplit]
(
    @RepParam varchar(MAX),
    @Delim char(1)
)
RETURNS @Values TABLE (Item UNIQUEIDENTIFIER)
AS
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 I try to just run the stored procedure by providing 4000+ values to @CompanyId parameter, it works fine and return all possible records.
But after I deployed the report in CRM, run it and click on view report it shows the following error, "An error has occurred"
I diagnosed it and found that 4000+ guid comma saperated values are too much for store procedure and IN statement:

Msg 8152, Level 16, State 10, Line 7 String or binary data would be
  truncated.

Please help!

A: When you make your @CompanyID parameter query. Add an option for 'Select All'. 
SELECT 'Select All' AS CompanyID, 1 as myOrder
 UNION
 Select CAST(CompanyID AS VARCHAR), 2 as myOrder
 FROM CompanyTable
 ORDER BY myOrder

With this the user doesn't have to select all 4000 values they can just select the 'Select All' option and it will select everything for them. And update your proc_getCompanyRecord to handle the new 'Select All' value of CompanyID.
CREATE PROCEDURE [dbo].[proc_GetCompanyRecord]
    (
    @Year AS VARCHAR(MAX) = NULL,
    @CoreCompanyId AS VARCHAR(MAX) = NULL,
    @CompanyId AS VARCHAR(MAX) = NULL,
    @OwnerId VARCHAR(MAX) = NULL
    )

--THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS

WHERE CoreCompany.CoreCompanyId in (SELECT * FROM ufnSplit (@CoreCompanyId, ','))
AND (Company.CompanyId in (SELECT * FROM ufnSplit (@CompanyId, ',')) OR Company.CompanyID = 'Select All')

