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>
One thought on “Split List Table-Valued Function”