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!

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!

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!

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!

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!