DataType Conversion using WHERE IN ( )

Colonel Angus · August 17th, 2004, 09:08 AM

I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@myList varchar(200)

Code:

SELECT column1
FROM table1
WHERE table1.ID IN (@myList)

When @myList is a single value, I get no errors. However, when @myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--Colonel

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

bmains · August 17th, 2004, 11:45 AM

Hey,

I had problems with that too... Execute it as dynamic SQL:

declare @SQL varchar(8000)
set @SQL = 'SELECT column1
FROM table1
WHERE table1.ID IN ' + @myList

exec @SQL

Brian

Colonel Angus · August 17th, 2004, 02:05 PM

Brian-

I was able to find another solution. What was going on was that SQL Server adds single quotes around the varchar parameter. I got around this by using the REPLACE function in the WHERE clause. My WHERE clause now looks like this:

WHERE table1.ID IN (REPLACE(@myList,'''',NULL))

I asked around, and of course your solution works, but I would lose some of the benefits of a stored procedure by using dynamic SQL. Specifically, the compiled execution plan.

Thank you for your comments.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Colonel Angus · August 18th, 2004, 07:43 AM

Brian-

My apologies - my solution did not work, I thought it had but I was mistaken.

Dynamic is the way to go.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.