[thelist] Stored procedure

[.jeff]

hi,

can you *please* trim the irrelevant bits from your replies.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: Stevenson Ngila
>
> Am using coldfusion. But i always prefer leaving the db
> processing to DB instead of using coldusion. But seems
> like there is no way out!
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

as ken has pointed out, you could do it in a sproc, but it's not easy.
additionally, you'll have to go to more trouble to either code in cf or in
the sproc to account for those times when the lists don't have the right
number of elements.  if you're just matching up elements from two lists and
performing inserts, i'd say a simple list loop should do the trick.  just
for kicks, put the list loop inside the <cfquery> tags so it's only one
connection to the database (provided you're database will support it).

<cfset string1 = ListToArray("peter,john,steve")>
<cfset string2 = ListToArray("mary,jane,janet")>

<cfquery name="insertnames" ...>
  <cfloop from="1" to="#ArrayLen(string1)#" index="i">
    INSERT INTO [table_name] (
                name_boy
              , name_girl)
         VALUES ('#string1[i]#'
              , '#string2[i]#')
  </cfloop>
</cfquery>

i'm guessing these two lists of names are the result of some form fields
that share the same name like checkboxes or multi-selects.  re-arranging the
ui might make it possible to get the matching boy/girl combination as
individual comma-delimited lists.  this would make it easier to identify
matches as they'd be in unique form fields.

<cfset form.string1 = "peter,mary">
<cfset form.string2 = "john,jane">
<cfset form.string3 = "steve,janet">

<cfquery name="insertnames" ...>
  <cfloop from="1" to="3" index="i">
    INSERT INTO [table_name] (
                name_boy
              , name_girl)
         VALUES ('#ListFirst(form['string' & i])#'
              , '#ListLast(form['string' & i])#')
  </cfloop>
</cfquery>

it's really just six of one or half a dozen of the other.  you're choice.

good luck,

.jeff

http://evolt.org/
jeff at members.evolt.org
http://members.evolt.org/jeff/