4

I am having a few problems using a stored procedure to search and return a list of matching IDs using the SQL IN operator. The issue I think is related to datatypes.

A parameter is passed to the stored procedure as a string '32,1,5,78,43' - This needs to be passed into the query as the IN operator to search the field Column1. The datatype for this field in Bigint.

DECLARE @TEST varchar(1000)
SET @TEST = REPLACE('32,1,5,78,43', '''','')

SELECT Column1, Column2 
FROM Table
WHERE Column1 IN(@TEST)

Trying to remove the quotes from the string doesn't appear to work, and I am getting an error back saying 'Error converting data type varchar to bigint.'

Running the code without the stored procedure, and putting the values directly into the IN operator (without the quotes) then does work correctly and returns the correct values. e.g. 

SELECT Column1, Column2 
FROM Table
WHERE Column1 IN(32,1,5,78,43)

Please could someone advise where I am going wrong here?

John Woo
  • 258,903
  • 69
  • 498
  • 492
Stef Robinson
  • 377
  • 3
  • 11
  • 1
    The answers are excellent here, but you should know that the reason your query isn't working as you would think is that your `IN` is comparing to one string rather than a set of integers. SQL Server cannot implicitly cast a comma-delimited string to a set of values. – Tim Lehner Mar 25 '13 at 13:22

4 Answers4

7

You can alternatively use dynamic sql on this:

DECLARE @TEST varchar(1000)
DECLARE @SQLQuery AS NVARCHAR(500)

SET @TEST = '32,1,5,78,43'

SET @SQLQuery = 'SELECT Column1, Column2 FROM Table WHERE Column1 IN('+ @TEST +')'
EXECUTE(@SQLQuery)
John Woo
  • 258,903
  • 69
  • 498
  • 492
3

One working way to write the integer-in-string-list test would be:

DECLARE @TEST varchar(1000)
SET @TEST = '32,1,5,78,43'

SELECT Column1, Column2 
FROM Table
WHERE ',' + @TEST + ',' LIKE '%,' + cast(Column1 as varchar(16)) + ',%'

A better way is to pass a strongly typed list of integers to the stored procedure using a table valued parameter.

Andomar
  • 232,371
  • 49
  • 380
  • 404
2

So you'll need to use dynamic SQL, but you don't need to escape the single quotes in your case, just do this:

declare @sql varchar(max)
set @sql = 'Select Column1, Column2 from Table where Column1 in (' + @test + ')'
execute (@sql)
Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
1

You could use a Split function to split your comma separated string into a temp table, then join to that.

Here's a good example of code to split the string: T-SQL: Opposite to string concatenation - how to split string into multiple records

You could also do a dynamic SQL, but that's generally less optimal.

Community
  • 1
  • 1
Jeremy Hutchinson
  • 1,975
  • 16
  • 26