MultiValue Parameters and Limitations in SQL Server

[erwin]

Hi,

I use a multi-value Parameter to select a subset of data in an IN clause.

On SQL Server if the User selects a lot/all of the possible values, this occasionally leads to the following error:

 

Too many parameters were provided in this RPC request. The maximum is 2100.

 

Since SQL Server does not support arrays as parameters, I assume the reporting engine does some magic to rewrite the parameters to

IN(@p1, @p2, @p3 ...)

Is there a way to workaround the problem, for example by using a temporary table with the parameter values?

Regards

Erwin

 

[Eric R | Senior Technical Support Engineer]

Hi Erwin,

You are correct. In order to use Multi-value parameters with a Stored Procedure, the value needs to be processed. One method of doing this is to use a Function. An example is shown in the How to use MultiValue Report Parameter in a SQL Query article. Let me provide a brief overview of it below.

Example

A string containing comma-separated values (CSV) can be passed from the report to the Stored Procedure. The Stored Procedure would then use a function to split the parameter values.

To create the CSV, use the built-in Join function. Let me review the SQL and Parameter Configuration next.

SQL

Note the Function and Store Procedure below.

USE AdventureWorks
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
   
-- FUNCTION TO SPLIT STRINGS.
CREATE FUNCTION splitstring
( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
   
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
   
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
   
  INSERT INTO @returnList
  SELECT @name
   
  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
   
INSERT INTO @returnList
SELECT @stringToSplit
   
RETURN
END
GO
  
-- STORED PROCEDURE.
CREATE PROCEDURE UseMultiValues
    @SelectedValues nvarchar(150) = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
   select * from HumanResources.Department
   where (@SelectedValues IS NULL) OR DepartmentID IN (select [Name] from dbo.splitstring(@SelectedValues))
END
GO

Report Parameter

Join the Selected Parameter Values into a CSV.

= IIf(Parameters.SelectedValues.Value Is Null,
       Null,
       Join(',',Parameters.SelectedValues.Value))

Please give this a try and let me know if you have any issues. Thank you for using the Telerik Reporting Forums.

Regards,

Eric R | Senior Technical Support Engineer
Progress Telerik

[erwin]

Unfortunately, modifying the database is out of the question in this case.
I need a solution that does works on a read-only database with just the reporting tool

 

Regards

Erwin

[Eric R | Senior Technical Support Engineer]

Hi Erwin,

I am sorry to hear the previous solution is unavailable for you.

Although, if I am understanding correctly, the limit of 2100 parameters for Stored Procedures is a SQL Server Limitation. In this case, an alternative is to use a SELECT statement Data Source within Telerik Reporting and avoid using the stored procedure. Doing this would provide additional options.

For example, MultiValue Parameters are supported when the Data Source is not a stored procedure. This way, it may be possible to use Cascading Parameters at the Data Source Level and achieve the desired filtering.

Ultimately, this depends on if skipping the stored procedure is an option and it would require the data be relational. Can you let me know if these options are available?

If these options are available and you would like me to investigate this further, can you send me a sample of the data so that I may know what it is shaped like?

If privacy is a concern, I can convert this to a support ticket so that any uploaded files and communication will not be available publicly.

In the meantime, please let me know if you need any additional information. Thank you and I look forward to your reply.

Regards,

Eric R | Senior Technical Support Engineer
Progress Telerik