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 |
|
Scottee25
Starting Member
3 Posts |
 Posted - 2006-12-19 : 10:13:10
|
| Hello,I am working with a stored procedure where I would like to pass a string(VARCHAR) with a series of values to use with the IN operator of my WHERE clause.Normally I would embed this series of values within the SQL statement of my application code and then have my SQLReader execute that statement. But due to some other processing that is taking place, a stored procedure seemed the most suitable method for performing this.As a brief example of my SELECT statement:SELECT * FROM MyTableWHERE ItemNum IN (@ItemNumList)The method I had tried(and which didn't work) was to decalre @ItemNumList as a VARCHAR in my list of input parameters for the Stored procedure. For purposes of demonstration let's say that the value of @ItemNumList is '001,003'. It would appear that since @ItemNumList is a VARCHAR that the WHOLE value is being evaluated as 1 value rather than tokenizing on each comma. If I replace @ItemNumList with 001,003 in the IN operator, I do get the expected results. Is there anyway for me to have @ItemNumList evaluated as a series of values rather than just 1 value?The only other ways I can think of working around this is to concatenate my entire SELECT statement into a VARCHAR and execute that or to tokenize @ItemNumList into a temporary table and perform a SELECT * subquery against this table in the IN operator. Am I overlooking something simple here that will allow me to do what I want without using the 2 methods I just described?Any help would be greatly appreciated! |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-19 : 10:15:21
|
| Take a look here:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830[/url]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Scottee25
Starting Member
3 Posts |
Posted - 2006-12-20 : 08:03:46
|
| Thank you for the link! It confirmed that one of the alternatives I suggested would have to be used.Thanks again! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-20 : 08:17:10
|
| Note that you can very well avoid dynamic sql in this caseMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-20 : 10:41:35
|
| <<Have I missed something? Wouldn't a a split function do the trick [without Dynamic SQL]?>>The question posted that It confirmed that one of the alternatives I suggested would have to be used.. As one of sommarskog's methods is based on Dynamic SQL, I thought he/she might have used that. Also doing this in Dynamic SQL is common way for all newbiesMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-20 : 10:56:36
|
| "doing this in Dynamic SQL is common way for all newbies"Indeedie. |
|
|
|
|
|
|
Using VARCHAR with IN operator