Query works as expected, SSRS finds error?

Question

This question was closed because someone thought it was the same issue as SSRS multi-value parameter using a stored procedure

But it is not. My report is not a stored procedure and thus, behaves differently. Also, this issue describes a result of getting no results if multi-valued params are used and that too is inaccurate for this scenario. So I'll try posting this again.

My report for the most part works. It is when I select more than one value from either of 2 specific params (@global, @manual) that I get this error:

Here is the SQL:

DECLARE @STATE VARCHAR(2) = 'mn'
    ,@START DATE = '6/1/2020'
    ,@END DATE = '7/1/2020'
    ,@GLOBAL VARCHAR(50) = 'indigent fee'
    ,@MANUAL VARCHAR(100) = '''misc charges'',''discount'''

DROP TABLE

IF EXISTS #customers
    ,#test
    SELECT DISTINCT ch.amount
        ,ch.vehicle_program_id
        ,c.customer_id
        ,ch.customer_charge_id
        ,ch.charge_type
    INTO #customers
    FROM customer c
    JOIN customer_charge ch(NOLOCK) ON c.customer_id = ch.customer_id
    JOIN service_history sh(NOLOCK) ON sh.customer_id = c.customer_id
    JOIN header h(NOLOCK) ON h.service_history_id = sh.service_history_id
    WHERE ch.entry_date BETWEEN @START
            AND @END
        AND ch.price_trigger_id IN (
            16
            ,15
            )
        AND ch.source_type = 1
        AND sh.service_type = 5
        AND h.is_duplicate = 0;

WITH CTE_global
AS (
    SELECT DISTINCT ch.charge_type
        ,'global' AS type
    FROM customer_charge ch
    JOIN store s ON ch.store_id = s.store_id
    JOIN address a ON a.id = s.address_id
    JOIN locality l ON a.locality_id = l.id
    WHERE l.region = @state
        AND ch.price_trigger_id = 16
    
    UNION ALL
    
    SELECT 'None'
        ,'global'
    )
    ,CTE_manual
AS (
    SELECT DISTINCT ch.charge_type
        ,'manual' AS type
    FROM customer_charge ch
    JOIN store s ON ch.store_id = s.store_id
    JOIN address a ON a.id = s.address_id
    JOIN locality l ON a.locality_id = l.id
    WHERE l.region = @state
        AND ch.price_trigger_id = 15
    
    UNION ALL
    
    SELECT 'None'
        ,'manual'
    )
SELECT DISTINCT c.last_name
    ,c.first_name
    ,vp.account_no
    ,cust.charge_type
    ,cust.amount
    ,sh.service_date
    ,s.store_name_short
    ,GLOBAL = g.charge_type
    ,manual = m.charge_type
INTO #test
FROM vehicle_program vp(NOLOCK)
JOIN vehicle v(NOLOCK) ON v.vehicle_id = vp.vehicle_id
JOIN service_history sh(NOLOCK) ON sh.vehicle_program_id = vp.program_id
    AND service_type = 5
JOIN customer c(NOLOCK) ON v.customer_id = c.customer_id
    AND c.customer_id = sh.customer_id
JOIN store s(NOLOCK) ON vp.current_store_id = s.store_id
JOIN #customers cust ON cust.customer_id = c.customer_id
    AND cust.vehicle_program_id = sh.vehicle_program_id
JOIN customer_condition cc(NOLOCK) ON c.customer_id = cc.customer_id
JOIN customer_charge ch(NOLOCK) ON ch.customer_id = c.customer_id
JOIN service_charge sc ON sc.service_history_id = sh.service_history_id
    AND sc.customer_charge_id = cust.customer_charge_id
JOIN header h(NOLOCK) ON h.service_history_id = sh.service_history_id
JOIN CTE_global g ON g.charge_type = ch.charge_type
JOIN CTE_manual m ON m.charge_type = ch.charge_type
WHERE cc.state_of_conviction = @state
    AND sh.service_date BETWEEN @START
        AND @END
    AND h.is_duplicate = 0

SELECT *
FROM #test
WHERE GLOBAL IN (
        CASE 
            WHEN @global IN ('None')
                THEN charge_type
            WHEN @global NOT IN ('None')
                THEN @global
            END
        )
    OR manual IN (
        CASE 
            WHEN @manual IN ('None')
                THEN charge_type
            WHEN @manual NOT IN ('None')
                THEN @manual
            END
        )

For clarity, the last bit in the query there is some logic to allow for these two params to be optional: so by selecting 'None' that param is rendered useless basically. It seems clear that the issue is with this last bit, specifically my WHERE clause using the CASE expression. When I remove that, I don't get the error, but I of course lose my logic. What's most confusing is that the error indicates an issue with a comma, but there's no comma in that part of the SQL?? Any help is going to be greatly appreciated.

Sorry but your query does not work correctly when executed via SSMS. You make a common mistake in assuming how IN works with a variable. The db engine does not magically expand the variable into a set for comparison purposes - and your usage is an addition problem. But when `@manual` contains your assigned value, the comparison `when @manual in ('NONE')` (or anything else - like 'DISCOUNT') will never be true. But that logic error seems to be covered up by the following WHEN expression which will always be true (if not null). — SMor, Jul 06 '20 at 19:32
TBH this all seems WAAAY over-complicated. But it would require an investment to figure out what you are trying to do. But you should not be [splattering your code with NOLOCK](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) and, if you do, you should not use the deprecated syntax for doing so. — SMor, Jul 06 '20 at 19:34

score 0 · Answer 1 · answered Jul 06 '20 at 19:33

0

Assuming users will only select 'None' from the list on it's own and never with another value then the following should work.

WHERE (GLOBAL IN (@Global) OR @Global = 'None')
      AND 
      (manual IN (@manual) OR @manual = 'None')

answered Jul 06 '20 at 19:33

Alan Schofield

19,839
3
22
35

David Browne - Microsoft · Answer 2 · 2020-07-06T20:57:52.403

this question was closed because someone thought it was the same issue

It is a dupe, but you kind of have to read between the lines in the other answers to apply it to this scenario. The point is that SSRS replaces multi-select parameters with delimited strings in the query body itself, and this transformation can lead either to unexpectedly getting no results, or in an illegal SQL query, depending on where the parameter marker appears in the original query.

I'll make it a bit clearer exactly what's going on. You can repro this behavior with this as your Data Set query:

drop table if exists #foo 

create table #foo(charge_type varchar(200) , global varchar(200))

    select *
    from #foo 
    WHERE GLOBAL IN (
            CASE 
                WHEN @global IN ('None')
                    THEN charge_type
                WHEN @global NOT IN ('None')
                    THEN @global
                END
            )

And configure @global as a parameter that allows multi-select. When the user selects multiple values SSRS transforms the query into:

drop table if exists #foo 

create table #foo(charge_type varchar(200) , global varchar(200))
select *
from #foo 
WHERE GLOBAL IN (
        CASE 
            WHEN N'a',N'b' IN ('None')
                THEN charge_type
            WHEN N'a',N'b' NOT IN ('None')
                THEN N'a',N'b'
            END
        )

Which fails with An expression of non-boolean type specified in a context where a condition is expected, near ','.

Query works as expected, SSRS finds error?

2 Answers2