Post reply

SELECT ... IN (@localVariable)

  • 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()?

  • June 21, 2006 at 4:52 pm

    #645164

    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

  • June 21, 2006 at 5:26 pm

    #645165

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

  • June 22, 2006 at 6:41 am

    #645230

    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 !!!**

  • June 22, 2006 at 8:41 am

    #645279

    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

  • June 22, 2006 at 12:18 pm

    #645340

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

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

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