SSRS multi-value parameter issue

Question

I have a parameter that passes these values to a stored procedure:

"CA, FL, NY, NJ, MA, CT, RI".

The issue I'm having is that if I select the "ALL" option it does not bring back any "CT" values. It does bring back "CT" if I select it individually. I noticed that there are no records for "MA" so I don't know if this has something to do with it.

I'm parsing the values using a comma delimited splitter in SQL.

Also, does anyone know how to test the parameter in SQL Server to see how it's actually passing the values?

The stored procedure is as follows:

CREATE PROCEDURE plicense
(@division VARCHAR(500))
AS
SELECT a.lastname,
a.firstname,
b.divisionname
from Table A
INNER JOIN Table B
ON A.practid = B.practid
WHERE B.divisionname in (SELECT item from dbo.fnsplit(@division, ','))

The SSRS parameter is populated by a stored procedure that looks like this:

SELECT DISTINCT divisionid,
divisionname
FROM TABLE A
UNION
SELECT -1, 'N/A'

Run a sql profiler on it to see the actual query that gets run. — twothreebrent, Apr 24 '17 at 14:40
Multi-valued parameters *don't* generate such strings. They generate `IN (a,b,c)` clauses. When you write `WHERE someID in @ids` and specify that `@ids` is a multi-valued parameter, SSRS will send `WHERE someID in (id1,id2,id3)` where `id1` etc are the parameters values selected by the user — Panagiotis Kanavos, Apr 24 '17 at 14:55
Also post your data set definition - *how* do you call the stored procedure? Why do you expect a CSV list in the first place? — Panagiotis Kanavos, Apr 24 '17 at 15:06
I updated my question to include the code. I'm not expecting a CSV list. When the SSRS report populates it does not include any "CT" records if the Select All default option is selected in the Division parameter. The stored procedure is being called in the Datasets in SSRS--I just populate the stored procedure name in Dataset Properties. I have many SSRS reports built the same way. This is the first time I've had this issue. — jackstraw22, Apr 24 '17 at 15:30

score 0 · Answer 1 · answered Apr 24 '17 at 16:05

0

The issue was in SSRS. I needed to add this to the parameter section of the main stored procedure: =join(Parameters!division.Value,",").

answered Apr 24 '17 at 16:05

jackstraw22

517
10
30

score -1 · Answer 2 · answered Apr 24 '17 at 14:47

-1

Create a function to separate the data as table

CREATE FUNCTION [dbo].[Split]
(
    @List NVARCHAR(2000),
    @SplitOn NVARCHAR(5)
)  
RETURNS @RtnValue TABLE 
(

    Id INT IDENTITY(1,1),
    Value NVARCHAR(100)
) 
AS  
BEGIN

WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN 

INSERT INTO @RtnValue (value)
SELECT
    Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1))) 


        SET @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
END

    INSERT INTO @RtnValue (Value)
    SELECT Value = LTRIM(RTRIM(@List))

    RETURN
END

In your report,

Where column in (select dbo.split (@param))

answered Apr 24 '17 at 14:47

Fabiano Carvalho

504
1
6
17

In a distant past I also had this same problem, and I managed to solve with this mallet – Fabiano Carvalho Apr 24 '17 at 14:49
That's not how SSRS multi-valued parameters work. They *don't* generate a single string, they are used to generate an `IN (a,b,c)` clause.This is also one of the slowest ways to split strings – Panagiotis Kanavos Apr 24 '17 at 14:53
Read again what I explained. – Fabiano Carvalho Apr 24 '17 at 14:56
I understood what you posted. As I said, that's not how SSRS multi-valued parameters work. Besides, the OP is already using a splitter function. A different splitter isn't going to fix anything if there is a bug in the stored procedure anyway – Panagiotis Kanavos Apr 24 '17 at 14:58
Are you suggesting it's a bug in the stored procedure and not something with SSRS? I'm just trying to figure out where to focus my attention on this issue. – jackstraw22 Apr 24 '17 at 15:03
1

Check Aaron Bertrands's [Split strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings). Using a `WHILE` results in very bad performance - it's essentially a cursor. The equivalent method using a CTE is typically slow – Panagiotis Kanavos Apr 24 '17 at 15:04
@jackstraw22 you haven't posted a stored procedure yet. And SSRS doesn't generate any such strings. It's impossible to help without any relevant info - the stored procedure and your data set definition. *How* do you call the sproc? – Panagiotis Kanavos Apr 24 '17 at 15:05
@PanagiotisKanavos I've included the stored procedures in the original question. – jackstraw22 Apr 24 '17 at 15:33

SSRS multi-value parameter issue

2 Answers2