Using IN clauses - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using IN clauses

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-06-17 : 10:59:33

This is my where clause in my stored proc:
WHERE lngIndex IN (@indexes)

@indexes is a csv string ("123,234,345") passed as a parameter from my ASP page. When I run the stored proc I get this error "Error converting the varchar value ... to a column of data type int". How do I get this to work?

Thanks.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-17 : 11:30:38

See if this will help

http://www.sqlteam.com/item.asp?ItemID=11499

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 12:30:27

and there is always

where charindex(',' + IngIndex + ',',',' + @indexes + ',')>0

Corey

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-06-17 : 12:48:40

Thanks guys.

Seventhnight, what is this supposed to do?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 12:53:17

try it and see:

Declare @myTable table (val int)
Insert Into @myTable values(1)
Insert Into @myTable values(2)
Insert Into @myTable values(3)
Insert Into @myTable values(4)
Insert Into @myTable values(5)
Insert Into @myTable values(6)
Insert Into @myTable values(7)

Declare @myFilter nvarchar(100)
Set @myFilter = '3,5,7'

Select * From @myTable Where charindex(',' + convert(nvarchar,val) + ',',',' + @myFilter + ',')>0

Corey

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-06-17 : 13:39:14

Thanks Corey. Your idea looks good as I don't have to modify my query that much. I tried it, but it seems to execute so slow.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-17 : 13:41:20

Did you try out the method that is described in the link that drymchaser posted?

http://www.sqlteam.com/item.asp?ItemID=11499

Tara

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-06-17 : 13:44:11

Yes, but I am using SQL SErver 7. The code only works on v. 2000.