Looping a list in SQL 2000 SPROC

[dj shane]

Hey guys, just trying to learn something new today and wanted to convert one of
my cf pages to a sproc. This page just sets up security for the application. In
Coldfusion, all I'm doing is looping over a list.

The list is generated from a group of checkboxes, and looks like this.

<cfif isDefined("form.tech")>
<cfloop index="i" list="#form.tech#">
<cfquery name="UpdateTech" datasource="#application.dsn#">
INSERT INTO Q_SecurityUser_TBL
(Employee_ID, Security_ID, Lob_ID)
VALUES(<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#i#">, 1,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Lob_ID#">)
</cfquery>
</cfloop>
</cfif>

How would I loop over this list in SQL?

[philh]

Hi Shane,
<cfquery name="UpdateTech" datasource="#application.dsn#">
INSERT INTO Q_SecurityUser_TBL
(Employee_ID, Security_ID, Lob_ID)
VALUES(<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#i#">, 1,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Lob_ID#">)
</cfquery>


It's best to issue as few DB transactions as possible. I'd construct a bulk
insert string from the list:


<cfset myquery = "INSERT INTO Q_SecurityUser_TBL
(Employee_ID, Security_ID, Lob_ID)
SELECT ">

<cfloop index="i" list="#form.tech#">
<cfset myquery = myquery & i &", 1," & lob_id & "UNION SELECT ">
</cfloop>

<cfset myquery = mid(myquery,1,len(myquery-13))>

<cfoutput>#myquery#</cfoutput><!--- to see what the loop constructed --->

<cfquery name="UpdateTech" datasource="#application.dsn#">
#myquery#
</cfquery>

HTH,