0

I'm trying to make this sample working but it fails every time and displays the bad response. Can you please explain to me why that is the case? Thanks

DECLARE @CARACS NVARCHAR(max) = 'DLV, DLC'

SELECT 
    CASE 
       WHEN 'DLV' IN (@CARACS) 
          THEN 'GOOD' 
          ELSE 'BAD' 
    END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cédric B
  • 77
  • 8
  • 4
    [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) You have multiple possibilites: TVP/splitting/XML/building custom query. Please choose the one that suits you best. – Lukasz Szozda Jul 12 '18 at 17:32
  • 1
    To answer your question, the only value that will match is `'DLV, DLC'` because that is the only choice you provided. You seem to be confused by _optimistic programming_ where you hope the code will do what you want, e.g. `7 = 'VII'`. – HABO Jul 12 '18 at 18:30
  • This is a very common mistake. The `IN` operator expect a **list of values**, but you are supplying a **single value** that happens to contain a list – Zohar Peled Jul 13 '18 at 05:58

2 Answers2

1

This is the problem... DECLARE @CARACS NVARCHAR(max) = 'DLV, DLC' . Only way this would work is splitting it up... because DLV will never equal 'DLV, DLC', to SQL Server 'DLV, DLC' is a single string.

DECLARE @CARACS1 NVARCHAR(max) = 'DLV', @CARACS2 NVARCHAR(max) = 'DLC'

SELECT 
    CASE 
      WHEN 'DLV' = @CARACS1 OR 'DLV' = @CARACS2
      THEN 'GOOD' 
      ELSE 'BAD' 
END
DanB
  • 59
  • 3
1

Here are 2 queries, if you are able to find the difference between them, then you will understand why your query is returning 'Bad' value:

SELECT 
    CASE 
       WHEN 'DLV' IN ('DLV, DLC') 
          THEN 'GOOD' 
          ELSE 'BAD' END


SELECT 
    CASE 
       WHEN 'DLV' IN ('DLV', 'DLC') 
          THEN 'GOOD' 
          ELSE 'BAD' END

Explanation:
'DLV,DLC' - is one value.

'DLV', 'DLC' - two values.

So, turn 'DLV,DLC' into several individual values, you need to use splitter function.

(I used Jeff Moden's splitter - DelimitedSplit8K)

DECLARE @CARACS VARCHAR(8000) = 'DLV,DLC'

SELECT 
    CASE 
       WHEN 'DLV' IN (select * from dbo.DelimitedSplit8K(@CARACS,',')) 
          THEN 'GOOD' 
          ELSE 'BAD' 
    END