Results 1 to 5 of 5

Thread: Using a variable with IN

  1. #1
    Dana Guest

    Using a variable with IN

    I am trying to create a stored proc that will allow me to accept a list into a variable and then use that variable in a where clause. Example

    CREATE PROCEDURE RPTCCLCallCount (@ICat varchar(100)) AS

    SELECT col1, col2, col3
    FROM table1
    WHERE field1 = X
    AND field2 IN (@ICat)


    This does NOT work even though it compiles and will run in query window. Someone PLEASE help!

    Thanks, thanks, thanks!

  2. #2
    Andel Guest

    Using a variable with IN (reply)

    Your query works for me, i'm not sure why it doesn't work for you. I'm assuming that col1, col2, col3, field1, and field2 are in the same table?


    ------------
    Dana at 1/24/2002 7:08:05 PM

    I am trying to create a stored proc that will allow me to accept a list into a variable and then use that variable in a where clause. Example

    CREATE PROCEDURE RPTCCLCallCount (@ICat varchar(100)) AS

    SELECT col1, col2, col3
    FROM table1
    WHERE field1 = X
    AND field2 IN (@ICat)

    This does NOT work even though it compiles and will run in query window. Someone PLEASE help!

    Thanks, thanks, thanks!

  3. #3
    Graham Guest

    Using a variable with IN (reply)

    Your query works for me as well.

    Is field2 a char/varchar field?

    Is the variable string formatted correctly?

    'a', 'b', 'c', 'd' = Correct
    a, b, c, d = Incorrect


    ------------
    Andel at 1/25/2002 2:15:03 AM

    Your query works for me, i'm not sure why it doesn't work for you. I'm assuming that col1, col2, col3, field1, and field2 are in the same table?


    ------------
    Dana at 1/24/2002 7:08:05 PM

    I am trying to create a stored proc that will allow me to accept a list into a variable and then use that variable in a where clause. Example

    CREATE PROCEDURE RPTCCLCallCount (@ICat varchar(100)) AS

    SELECT col1, col2, col3
    FROM table1
    WHERE field1 = X
    AND field2 IN (@ICat)

    This does NOT work even though it compiles and will run in query window. Someone PLEASE help!

    Thanks, thanks, thanks!

  4. #4
    Dana Guest

    Using a variable with IN (reply)

    It does not matter if the variable is a "numeric string" (have to use convert) or a varchar string. If I try to do what I showed you guys with more than one value in the string, it will not return records. I can not, for other reasons, use the creation of a dynamic SQL statement and the exec command. I had been messing around with this in query analyzer as follows:

    DECLARE @var varchar(100)

    SELECT @var = '"FO","BD","LM"&#39 ;

    SELECT * FROM MyTable
    WHERE varmatchfield IN (@var)

    Using the above - SELECT @var = '"FO","BD","LM"&#39 ; - returns no records whereas if I change the SELECT @var to '"FO"', it works fine. Someone pointed out to me late last night that SQL sees the @var as a SINGLE value which is why it works when there is on value in the @var and not when there are multiple. I am using 6.5 by the way (if that matters).

    Dana


    ------------
    Graham at 1/25/2002 4:59:58 AM

    Your query works for me as well.

    Is field2 a char/varchar field?

    Is the variable string formatted correctly?

    'a', 'b', 'c', 'd' = Correct
    a, b, c, d = Incorrect


    ------------
    Andel at 1/25/2002 2:15:03 AM

    Your query works for me, i'm not sure why it doesn't work for you. I'm assuming that col1, col2, col3, field1, and field2 are in the same table?


    ------------
    Dana at 1/24/2002 7:08:05 PM

    I am trying to create a stored proc that will allow me to accept a list into a variable and then use that variable in a where clause. Example

    CREATE PROCEDURE RPTCCLCallCount (@ICat varchar(100)) AS

    SELECT col1, col2, col3
    FROM table1
    WHERE field1 = X
    AND field2 IN (@ICat)

    This does NOT work even though it compiles and will run in query window. Someone PLEASE help!

    Thanks, thanks, thanks!

  5. #5
    Victor Guest

    Using a variable with IN (reply)

    Dana, I'm not sure how you're getting the "IN" clause to work without its subject being a "SELECT" (insteead of a plain string), but try this instead:

    DECLARE @var varchar(100)
    SELECT @var = '"FO","BD","LM"&#39 ;

    SELECT *
    FROM MyTable
    WHERE CHARINDEX(varmatchfield, @var) > 0

    This will tell you if "varmatchfield" is contained within "@var"...

    P.S.: "varchar"s are naughty! Makes SS work harder to track them.

    ------------
    Dana at 1/25/2002 11:20:13 AM

    It does not matter if the variable is a "numeric string" (have to use convert) or a varchar string. If I try to do what I showed you guys with more than one value in the string, it will not return records. I can not, for other reasons, use the creation of a dynamic SQL statement and the exec command. I had been messing around with this in query analyzer as follows:

    DECLARE @var varchar(100)

    SELECT @var = '"FO","BD","LM"&#39 ;

    SELECT * FROM MyTable
    WHERE varmatchfield IN (@var)

    Using the above - SELECT @var = '"FO","BD","LM"&#39 ; - returns no records whereas if I change the SELECT @var to '"FO"', it works fine. Someone pointed out to me late last night that SQL sees the @var as a SINGLE value which is why it works when there is on value in the @var and not when there are multiple. I am using 6.5 by the way (if that matters).

    Dana


    ------------
    Graham at 1/25/2002 4:59:58 AM

    Your query works for me as well.

    Is field2 a char/varchar field?

    Is the variable string formatted correctly?

    'a', 'b', 'c', 'd' = Correct
    a, b, c, d = Incorrect


    ------------
    Andel at 1/25/2002 2:15:03 AM

    Your query works for me, i'm not sure why it doesn't work for you. I'm assuming that col1, col2, col3, field1, and field2 are in the same table?


    ------------
    Dana at 1/24/2002 7:08:05 PM

    I am trying to create a stored proc that will allow me to accept a list into a variable and then use that variable in a where clause. Example

    CREATE PROCEDURE RPTCCLCallCount (@ICat varchar(100)) AS

    SELECT col1, col2, col3
    FROM table1
    WHERE field1 = X
    AND field2 IN (@ICat)

    This does NOT work even though it compiles and will run in query window. Someone PLEASE help!

    Thanks, thanks, thanks!

Posting Permissions

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