Sql server, sql query, "IN" statement not working

Question

I have a query where comma seperated barcodes are provided. But for some unknown reason it is not working:

Below is the code that does not work

DECLARE @txtBarcode VARCHAR(MAX)

SET @txtBarcode = '2003824,2003825';

SELECT [Scan_Curr_Location] 
FROM BarCode_Location_Current 
WHERE Scan_Curr_BarCodeNo IN (@txtBarcode) 
ORDER BY [Scan_Curr_BarCodeNo] ASC

However when I change it to a single barcode it works fine i.e:

SET @txtBarcode = '2003824';

Please advice.

Thanks!

See the accepted answer here: https://stackoverflow.com/questions/3516064/creating-a-sql-table-from-a-comma-concatenated-list. I always loved the way this goes about solving the issue. — VBlades, Jun 20 '14 at 11:09

score 3 · Answer 1 · answered Jun 20 '14 at 11:03

IN does not work like that. It takes a comma separated list, not a string with comma separated values.

This would work:

SELECT [Scan_Curr_Location] FROM BarCode_Location_Current WHERE Scan_Curr_BarCodeNo 
IN ('2003824', '2003825') ORDER BY [Scan_Curr_BarCodeNo] ASC

score 1 · Answer 2 · edited Jun 20 '14 at 12:40

As documented here http://www.w3schools.com/sql/sql_in.asp :-

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN(value1,value2,...);

so in your case you should make it comma separated like this:

SELECT [Scan_Curr_Location] 
FROM BarCode_Location_Current 
WHERE Scan_Curr_BarCodeNo IN ('2003824', '2003825') 
ORDER BY [Scan_Curr_BarCodeNo] ASC

score 0 · Answer 3 · answered Jun 20 '14 at 11:05

Another approach is to use LIKE instead of IN :

DECLARE @txtBarcode VARCHAR(MAX)

SET @txtBarcode = '2003824,2003825';

SELECT [Scan_Curr_Location] 
FROM BarCode_Location_Current 
WHERE @txtBarcode LIKE '%' + Scan_Curr_BarCodeNo + '%'
ORDER BY [Scan_Curr_BarCodeNo] ASC

Note the wildcards '%' around the column you are searching...

Sql server, sql query, "IN" statement not working

3 Answers3