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.
| Author |
Topic |
|
sqlbug
Posting Yak Master
201 Posts |
 Posted - 2010-09-22 : 14:09:24
|
| Hi, I have a parameter for a procedure that contains multiple integer ids separated by comas, like:@BOOK_LIST = "1023,1034,1099,1121,1341".The procedure then uses it like:AND BOOK_ID IN (@BOOK_LIST). For some reason - I chose not to send the this parameter as xmldocBut since the ids are Integers and the parameter is a varchar, I am getting errors.How can I get the values like 1023,1034,1099,1121,1341 and use them likeAND BOOK_ID IN (@BOOK_LIST)?I need to retrieve each value from the string and put them in an integer array, then use it in the IN clause.Is it possible?Thanks. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-09-22 : 14:34:03
|
| use the ParseValues function found on this site and join to the result set. You can't do IN(@Variable)JimEveryday I learn something that somebody else already knew |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-09-22 : 15:22:49
|
| If you can you might want to look into passing a table-valued parameter. That should be a better choice over delemited strings or XML. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-09-23 : 05:46:42
|
| Another way is','+@BOOK_LIST+',' like '%,'+cast(BOOK_ID as varchar(10))+',%'or use dynamic sqlMadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-09-23 : 12:25:29
|
quote:
Originally posted by sqlbug
Hi, I have a parameter for a procedure that contains multiple integer ids separated by comas, like:@BOOK_LIST = "1023,1034,1099,1121,1341".The procedure then uses it like:AND BOOK_ID IN (@BOOK_LIST). For some reason - I chose not to send the this parameter as xmldocBut since the ids are Integers and the parameter is a varchar, I am getting errors.How can I get the values like 1023,1034,1099,1121,1341 and use them likeAND BOOK_ID IN (@BOOK_LIST)?I need to retrieve each value from the string and put them in an integer array, then use it in the IN clause.Is it possible?Thanks.
seehttp://visakhm.blogspot.com/2010/02/parsing-delimited-string.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Getting data as integer from a String