Usually we have to loop through lists to insert or update data, and some of us choose to use ColdFusion tag CFLOOP with an inner query to do that job. That will cause several connections to the database (one for each member of the loop). One way to avoid that, other than create a huge CFQUERY tag with several inserts, is to write a store procedure and with just one connection to the database, let the SQL take care of the list loop and do its job of inserting or updating data.
You may download the stored procedure here.
Suppose you have your selection of itens in a list and need to insert it to a shopping cart:
“ProductID|Qty,ProductID|Qty,…”
So, just for test let’s set the list here:<cfset addCartValues="P555|2,P444|1,P333|2" />In ColdFusion a beginner coder would do this:<cfloop list="#addCartValues#" index="prodList">
<cfset productID = listFirst(prodList, "|") />
<cfset qty = listLast(prodList, "|") />
<cfquery name="qAdd" datasource="#request.mainDSN#">
insert into shoppingCart (
ProductID
, Qty
, Price )
values (
'#productID#'
, #qty#
, (select top 1 price from products where productID = '#productID#')
</cfquery>
</cfloop>
The code above, as I said before, will cause ColdFusion to make several connections to the database (one for each product of the list). So, let’s create a stored procedure and pass the entire list as parameter like:<cfstoredproc procedure="dbo.addToCart" datasource="#request.mainDSN#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#addCartValues#" type="in" />
<cfprocparam cfsqltype="CF_SQL_CHAR" value="," type="In" />
</cfstoredproc>
Here is the procedure:create procedure [dbo].[addToCart]
@productList varchar(3000)
, @delim char(1)
as
begin
set ANSI_NULLS on
set QUOTED_IDENTIFIER on
set NOCOUNT on;
declare
@pos int
, @productID varchar(15)
, @prodQty varchar(15)
-- create temporary table to hold the transaction
declare @@temp table (productID varchar(15), qty int)
if ((@productList is not null) and (len(@productList) > 0))
begin
-- let's loop through the list
set @pos = charIndex(@delim,@productList)
while @pos > 0
begin
-- get one block of productID|qty from the loop
set @prodQty = substring(@productList, 1, @pos -1)
-- strip this block from the list
set @productList = substring(@productList, @pos + 1, len(@productList) - @pos)
-- now find the productID and qty
set @pos = charIndex('|', @prodQty)
set @productID = substring(@prodQty, 1, @pos -1)
set @qty = substring(@prodQty, @pos +1, len(@prodQty) - @pos)
-- insert those values to the temp table
insert into @@temp (
productID
, qty
, price
) values (
@productID
, cast(@qty as int)
-- for security reasons you should always get the price from the database, not from the user input
, (select top 1 price from products where productID = @productID)
)
-- find the new position for the next block
set @pos = charindex(@delim, @productList)
end
-- insert the last block from product list
set @pos = set @pos = charIndex('|', @productList)
set @productID = substring(@productList, 1, @pos -1)
set @qty = substring(@productList, @pos +1, len(@productList) - @pos)
insert into @@temp (
productID
, qty
, price
) values (
@productID
, cast(@qty as int)
, (select top 1 price from products where productID = @productID)
)
-- now, insert all products from temp table to the shopping cart
insert into shoppingCart (
productID
, qty
, price
) select
productID
, qty
, price from @@temp
-- the temporary table will disappear automatically at the end of this procedure
end
end
why not simply put the cfloop inside the cfquery & push a big batch of sql at the server (after wrapping in a SET NOCOUNT block)?
Yes, you could cfloop inside the query, but that would send a huge string to the SQL connection too, or, you could also transform this stored procedure into a function and just call it in the query like:
insert into shoppingCart (productID, qty, price)
select productId, qty, price from splitList(‘#productList#’, ‘,’)