SELECT ... IN (@localVariable)

Question

SELECT ... IN (@localVariable)

Kenneth Gladden

Old Hand

Points: 303
More actions
June 21, 2006 at 3:52 pm

#90232

I get the error:
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,3,5' to a column of data type int.
DECLARE @lstPersonType varchar(50)
SET @lstPersonType = '1,3,5'
SELECT   Workers.WorkerID
FROM Workers
  INNER JOIN tblPeople_tblPeopleTypes
   ON Workers.WorkerID = tblPeople_tblPeopleTypes.intPersonID
WHERE tblPeople_tblPeopleTypes.intPersonTypeID
IN (@lstPersonType)
How can I use a variable that has multiple values in an IN()?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

IN will only accept literals i.e. the actual values spelled out right there between the brackets(). You could, but almost certainly shouldn't, use dynamic SQL to get the literals between the brackets. Better to 'pivot' the values into a column in a table or table variable (or place them in a column as they are first generated if possible - maybe a TVF or a client-side recordset,...)

that way you can join to the column rather than using IN().

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Kenneth Gladden Old Hand Points: 303 More actions · Answer 2

Thanks for the information; that was what I was missing! I will try an alternative.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

You may also want to read Erland Sommarskog's (really comprehensive) article on lists & arrays that specifically addresses this!!!

**ASCII stupid question, get a stupid ANSI !!!**

stax68 SSChampion Points: 11711 More actions · Answer 4

Actually, a correction. The IN() function will accept a list of variable names separated by commas. In fact that's why you got the error you did. SQL parsed and compiled the code OK, but exepected your variable to be the sole element in a list of integer variables (to be precise: a list of variables containing values and having datatypes such that they can be implicitly cast into integers). So when it tried implicit conversion on the string value and found a comma in there, it threw an exception.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Kenneth Gladden Old Hand Points: 303 More actions · Answer 5

Kenneth Gladden

Old Hand

Points: 303

June 22, 2006 at 12:18 pm

#645340

The lists and arrays article is perfect! Thanks!!!