Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: using IN and a Variable

    I am trying to select values based on a string I have whos values are seperated by commas. I want to use IN, but it is just not working. My string has "alvin,simon" and I have tried all types of things with now luck.
    ------------------------------------
    EXEC myproc 'alvin,simon'

    PROCEDURE myproc @inp_values
    AS
    BEGIN

    SELECT *
    FROM table1
    WHERE table1.field in (@inp_values)

    END
    ----------------------------------

    Any ideas?

  2. #2
    Join Date
    Sep 2004
    Location
    Stockholm, Sweden
    Posts
    28
    The IN operator uses a value and compares if it is in a _set_ of values.

  3. #3
    Join Date
    Sep 2004
    Location
    Stockholm, Sweden
    Posts
    28
    Not sure what you try to do but it looks like:

    select * from table1
    where field IN (select field from table1 where field = @inp_values)

    but then you can of course skip the IN and only use the inner select

    select field from table1 where field = @inp_values

    If I missunderstod, please explain you problem and what you want the query to return.

    /Mats

  4. #4
    Join Date
    Nov 2004
    Posts
    3
    My problem is a little different as I am familiar with using IN. Say I have a set of values in a variable (which I will call @mysetofvalues). Well, when I run this query...

    SELECT * FROM table1 WHERE table1.key IN (@mysetofvalues)

    It will work if @mysetofvalues = "open" or @mysetofvalues = "closed", but if I do @mysetofvalues = "open,closed" or @mysetofvalues = "'open','closed'" it will not work.

    And if I do this query

    SELECT * FROM table1 WHERE table1.key IN ('open','closed')

    Then it does bring back the correct values. So my issue is how to correctly assign my values to the variable.

    Any ideas?
    Last edited by alvindb; 11-29-04 at 10:36.

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You have to use dynamic SQL, e.g.

    PROCEDURE myproc @inp_values
    AS
    BEGIN

    Exec( 'SELECT *
    FROM table1
    WHERE table1.field in (' + @inp_values + ')'

    END

    I haven't tested this, but it should be something like that.
    Thanks,

    Matt

  6. #6
    Join Date
    Nov 2004
    Posts
    3
    Quote Originally Posted by MattR
    You have to use dynamic SQL, e.g.

    PROCEDURE myproc @inp_values
    AS
    BEGIN

    Exec( 'SELECT *
    FROM table1
    WHERE table1.field in (' + @inp_values + ')'

    END

    I haven't tested this, but it should be something like that.
    it worked! (of course). A million thank you's MattR!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •