Continuing my previous blog about splitting a list to insert data into a table, we could transform the stored procedure into a table-valued function that you can flaunt using your best hosting.
Using the same example list as before, let’s create the function for this list
<cfset addCartValues=”P555|2,P444|1,P333|2″ />
You may download the function here.


create function [dbo].[splitList](
@productList nvarchar(4000),
@listDelim char(1),
@itemDelim char(1)
)
returns @retVal table (
Id int identity(1,1)
,   productID varchar(100)
,   qty integer
,   price money
)
as
begin
declare
@pos int
,   @productID varchar(15)
,   @prodQty varchar(15)
,   @item varchar(50)
if ((@productList is null) or (len(@productList) = 0))
begin
select null as productID, null as qty, null as price
return
end
While (charIndex(@listDelim,@productList)>0)
begin
set @pos = charIndex(@listDelim, @productList)
set @item = substring(@productList, 1, @pos -1)
set @productList = substring(@productList,@pos +1, len(@productList) - @pos)
set @pos = charIndex(@itemDelim, @item)
set @productID = substring(@item, 1, @pos -1)
set @qty = substring(@item, @pos +1, len(@item) - @pos)
Insert Into @retVal (productID, qty, price)
select
@productID
,   cast(@qty as int)
,   (select top 1 price from products where productID = @productID)
end
set @pos = charIndex(@itemDelim, @productList)
set @productID = substring(@productList, 1, @pos -1)
set @qty = substring(@productList, @pos +1, len(@productList) - @pos)
insert into @retVal (productID, qty, price)
select
@productID
,   cast(@qty as int)
,   (select top 1 price from products where productID = @productID)
return
end

Now we can call the function from inside the query like this:
<cfquery name="cart" datasource="#request.mainDSN#">
insert into shoppingCart (productID, qty, price)
select productId, qty, price from splitList('#productList#', ',','|')
</cfquery>