Coldfusion loop over insert query values

Question

I am trying to loop over an insert query. I'm looping over the values to the return results are within a list.

<cfquery datasource="#OLMSdatasourceWrite#" result="myResult">
    INSERT INTO OLMS_Data_RatioScenarios
    (
        OLMS_Account_ID,
        OLMS_RatioScenario_Name
    )
    VALUES
    (
    <cfloop list="#AccountListWithSettings#" index="CurrentAccount">
        (<cfqueryparam cfsqltype="cf_sql_numeric" value="#CurrentAccount#" maxlength="255">, <cfqueryparam cfsqltype="cf_sql_clob" value="#requestBody.value#" maxlength="255">)
        <cfif CurrentAccount GT 1>
           ,
        </cfif>
    </cfloop>
    )
</cfquery>

<cfoutput>Inserted ID is: #myResult.generatedkey#</cfoutput>

The problem im running into here is that it keeps putting a "," after the final iteration

VALUES ( ( (param 1) , (param 2) ) , ( (param 3) , (param 4) ) , ( (param 5) , (param 6) ) , ) 

I need help figureing out the cfif statement to prevent this(NOTE: if i put the cfif above the query params it started the loop out like (,(param 1),(param 2),

Answer 1

You need to track the number of items in the list and check that the current item's position is less than the length of the list:

<cfset numItems = ListLen(AccountListWithSettings)>
<cfset i = 1>

<cfloop list="#AccountListWithSettings#" index="CurrentAccount">
  <!--- Stuff inside of loop --->

  <!--- ...and then --->
  <cfif i lt numItems>
    ,
  </cfif>

  <cfset i++>
</cfloop>

Update, per @Leigh's comment: you can also take this approach. I add it as a 2nd approach because even though it's simpler, it's not usually how my mind thinks through this particular problem. To each his own. :)

<cfset i = 1>

<cfloop list="#AccountListWithSettings#" index="CurrentAccount">
  <cfif i gt 1>
    ,
  </cfif>

  <!--- Stuff inside of loop --->

  <cfset i++>
</cfloop>