Link to home
Start Free TrialLog in
Avatar of soadfan
soadfan

asked on

how do i pass in a list or array to sql server?

hi,

i need to pass in a list/array to a sql server stored proc, then loop thru the list in sql server, and put it into where clause in a query. for example my list in coldfusion might look like this:
xxx, yyy, zzz

i need to pass this in to the stored proc (unsure of what cfsqltype to use)

after it is passed,  i need put each element in the list as dynamic conditions in my where clause.
so for example my where clause would be as follows:

WHERE
substring([catalogProductID],1,6) + substring([catalogproductid],10,16)) Like '%FIRSTELEMENTINTHELIST%
OR
  substring([catalogProductID],1,6) + substring([catalogproductid],10,16)) Like '%SECONDELEMENTINTHELIST%  etc, etc...

so if the list i passed in contained xxx, yyy, zzz then i need my where clause in the stored proc to look like this:

WHERE
substring([catalogProductID],1,6) + substring([catalogproductid],10,16)) Like '%xxx%
OR
  substring([catalogProductID],1,6) + substring([catalogproductid],10,16)) Like '%yyy%  
OR
  substring([catalogProductID],1,6) + substring([catalogproductid],10,16)) Like '%zzz%  

so first, what cfsqltype do i use to pass in the list, then how do i loop through each element in the list in sql server in my 'where' clause hence dynamically building the conditions?
Avatar of Tacobell777
Tacobell777

you cannot pass an array to SQL, it does not know arrays..
You could pass an XML structure that acts as an array, but you probably don't want to go there.

Why not just do arrayToList() ?
it converts your array to a list value which you can easily pass to sql..
your probably better of doing this query within CF

WHERE
<cfloop from="1" to="#arrayLen( yourArray )#" index="i">
substring([catalogProductID],1,6) + substring([catalogproductid],10,16)) Like '%#yourArray[ variables.i ]#%
</cfloop>


This whole substring thing is not a good idea to start with, probably bad design...
Avatar of soadfan

ASKER

hi tacobell777,

yes, i thought about doing it in cold fusion, then just passing it in as one long string, but i thought there was a better way. btw, why are substrings bad design? im relativly new to CF/sqlserver
If you are searching 10.000 records then for each record the db will need to perform this string manipulation, which becomes expensive.. You should have probably split the string into one or more columns when you need to do this..

Also the keyword LIKE is also very expensive and will return results that have the value of your array in them, i.e.

LIKE '%TEST%'

could return
- MYTEST
- TEST
- SOMETEST
- ONETESTTWO

not sure about your needs but you want to use = if you want to get exact records..
Avatar of soadfan

ASKER

hi,

what do u mean by 'You should have probably split the string into one or more columns'?
thanks
ASKER CERTIFIED SOLUTION
Avatar of Tacobell777
Tacobell777

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
is it clear?