0

We have a report that has 4 multi-value parameters. Each value would be 20 characters and some parms have > 30 choices. If a user should select All, passing the parameters as &dept=10&dept=20, etc. won't work because it will exceed the limit of 2048 for a URL. Is there another way to pass them? Here is what part of the URL looks like with just one value for each parm except Department:

... &p_IncidentStartDate=2015-01-01&p_IncidentEndDate=2016-02-25&p_DepartmentId=QDP00000000000000041&p_DepartmentId=QDP00000000000000008&p_DepartmentId=QDP00000000000000011&p_TouchpointId=QSE00000000000000075&p_GeneralIssueId=DSE00000000000000021&p_SpecificComplaintId=DSE00000000000000054&p_IsSubmitted=1&p_formGiftCardStatus=Pending

The stored procedure that is used in the dataset uses a "SplitString" function to separate the parameters with commas. So, when if I run the proc manually, I pass parms like this:

enter image description here

Is there some other way to pass the parameters other than one value at a time? (I've read lots of posts but can't find an answer that works.)

Laurel
  • 67
  • 8

1 Answers1

0

For one, you could have a manual option to select called "All" and change your query's logic to look something like this:

SELECT *
FROM TABLE
WHERE (p_DepartmentId IN (@p_DepartmentId) OR @p_DepartmentId = 'All')

I assume p_DepartmentId is the PK? It's unfortunate it's so long. I would look into the validity of this answer. But I think this latter solution involves a lot, lot of extra work. It might be easier to create a custom "All" entry like I've shown above. Just bake it into the query that returns the dataset for the departments, like this.

SELECT 'All' AS p_DepartmentId
UNION
SELECT p_DepartmentId FROM TABLE
Community
  • 1
  • 1
Langosta
  • 487
  • 3
  • 16
  • I think this has gotten more complicated. No only do we need to be able to "Select ALL", we need to be able to select many options or select nulls (the field is optional). I think we are going to have to use dynamic SQL. One of the tricks is that if it is a multi-value parameter, I can't allow NULL values. – Laurel Mar 01 '16 at 14:10