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 |
|
moorecg
Starting Member
1 Post |
 Posted - 2005-10-02 : 14:43:42
|
| Have the following code in a Stored Procedure:SELECT nField FROM nTable WHERE aField IN (w,x,y,z)I want to pass the value of w,x,y,z as a single parameter: SELECT nField FROM nTable WHERE aField IN (@param)If I set @param = "w", the procedure works.If I set @param = "w,x" or "w,x,y,z" the procedure fails.Looks like a data type setting problem, but I can't find a combination that works.Thanks in advance. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-02 : 15:31:16
|
| And you won't. You could either:1. Use dynamic SQL (not preferred).2. Use a user-defined function to convert the delimited list to a table (preferred).3. Pass in multiple variables if there is a set limit and join to a UNION of them.If you need help with two, search the forum or look at the articles section of the website for "delimited list". Here is a search link for the articles section that will give you several good examples:http://www.sqlteam.com/searchresults.asp?SearchTerms=delimited+list&SUBMITs1=SearchMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-03 : 01:30:22
|
| Hi moorecg, Welcome to SQL Team!I would usually use (2)You might also consider:SELECT nField FROM nTable WHERE ',' + @param + ',' LIKE '%,' + aField + ',%'if aField is a string column (or use CONVERT / CAST)Kristen |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-10-03 : 03:49:51
|
| If there is not too much possible values for aField you can build a table with all possible param values, make index on paramValue column and then join new table with nTable. This approach is good for a Status like columns, but you would need to make sure new table is updated at the same time as aFieldDomainLookupTable. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-03 : 04:57:17
|
in addition, you may want to check patindex and charindex if the list is not that long quote:
Originally posted by Kristen
Hi moorecg, Welcome to SQL Team!I would usually use (2)You might also consider:SELECT nField FROM nTable WHERE ',' + @param + ',' LIKE '%,' + aField + ',%'if aField is a string column (or use CONVERT / CAST)Kristen
--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-03 : 05:56:12
|
| Blimey ... looks like I need to do a TEST !!Kristen |
|
|
|
|
|
|
|
|
Pass data to IN statement