-1

The following query works :

Declare @Product varchar(max) = '0022'

select * 
from Prod 
where ProdId in (@Product)

The following also works :

select * 
from Prod 
where ProdId in (0022,0033)

But the below doesn't work : 

Declare @Product varchar(max) = '0022,0033'

select *  
from Prod 
where ProdId in (@Product)

What am I doing wrong ? How can I fix it ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CodeNinja
  • 3,188
  • 19
  • 69
  • 112
  • That isn't how parameters work. They hold a scalar value and can't be used like this. You have a few options. You can pass in a table valued parameter, you can split your string, or you can use dynamic sql. – Sean Lange Mar 03 '16 at 19:39
  • 2
    The `IN` operator expects a **list of something** - like a list of integers - but what you're providing it in the last sample is a **single string** - not a list of anything..... you'd have to first **split up** that string into a list (table variable) of integer values, and *then* use those values in your `IN` operator.... – marc_s Mar 03 '16 at 19:48

2 Answers2

1

You cannot do this directly but there is a work around. Michelle Ufford has an excellent article in SQLFool on a function named "dba_parseString_udf" that will do what you need.

Oddly, you can do this (e.g. pass a comma delimited string to an IN clause) in a SQL statement that is embedded in SSRS when using a multi-select list.

PseudoToad
  • 1,504
  • 1
  • 16
  • 34
0

If your codes are all 4 digits you could use CHARINDEX to check for the code in the string.

SELECT *  
FROM Prod 
WHERE CHARINDEX(ProdId, @Product) > 0

If the codes can be of different lengths, add a comma before and after @Product so that you wont get 12345 when searching for 123.

SET @Product = ',' + @Product + ',' 

SELECT *  
FROM Prod 
WHERE CHARINDEX(',' + ProdId + ',' , @Product) > 0

The more correct way to do it would be to create a temp table and insert the values:

SELECT CAST('0022' AS VARCHAR(20)) AS CODE
UNION 
SELECT CAST('0033' AS VARCHAR(20)) AS CODE
INTO #TEMP_CODES

SELECT * 
FROM Prod 
WHERE ProdId IN (SELECT CODE FROM #TEMP_CODES)
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39