SQL Server IN Operator using varchar and bigint

Question

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?

Answer 1

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)