Listing 1

<CFQUERY NAME="getCost"
DATASOURCE="#request.site.ds#"
DBTYPE="#request.site.dbtype#">
SELECT Cost
FROM Items
WHERE ItemNo = 20
</CFQUERY>

<CFIF getCost.Cost GT 0>
<CFQUERY>
. . .
</CFQUERY>
<CFELSEIF getCost.Cost EQ 0>
<CFQUERY>
. . .
</CFQUERY>
<CFELSE>
<CFQUERY>
. . .
</CFQUERY>
</CFIF>

Listing 2

<CFSTOREDPROC
PROCEDURE="ChangeCost"
DATASOURCE="#request.admin.ds#">
<CFPROCPARAM
TYPE="In"
CFSQLTYPE="CF_SQL_INTEGER"
DBVARNAME="@ItemNo"
VALUE="#Attributes.ItemNo#"
NULL="No">
<CFPROCPARAM
TYPE="In"
CFSQLTYPE="CF_SQL_MONEY"
DBVARNAME="@Cost"
VALUE="#Attributes.Cost#"
NULL="No">
</CFSTOREDPROC>

Listing 3

CREATE PROCEDURE [pr_AddItemEvents]

@ItemNo [int],
@EventList [varchar](40),
@EventValue [int]

AS

DECLARE @ListLength [int],
@CommaCount [int]

SET
@ListLength =
Len(@EventList)

/* Start the loop over the list */
WHILE @ListLength >0
BEGIN

/* Determine where our first comma occurs */
SET
@CommaCount = CHARINDEX(',', @EventList)

/* If there is a comma, take the first value */
IF @CommaCount > 0
BEGIN
SET
@EventValue =
SubString(@EventList, 1, @CommaCount-1)
END

/* If not, take what's left */
ELSE
BEGIN
SET @EventValue = @EventList
END
/* Run the query */
INSERT INTO Event_Items_Xref
(Item_Fk,
Event_Fk)
VALUES
(@ItemNo,
@EventValue)

/* If there was a comma, remove the first value and its trailing comma */
IF @CommaCount > 0
BEGIN
SET
@EventList =
Right(@EventList, @ListLength-@CommaCount)
END

/* Otherwise, delete everything left in the list */
ELSE
BEGIN
SET @EventList = ''
END

/* Set the new list length so the loop knows if it should run again */
SET @ListLength = Len(@EventList)
END