Listing 1:

CREATE PROCEDURE spGS_App_Order

@vchOrderNote varchar(20),
@monGrandTotal money
@intOrderId int OUTPUT /* lets have this proc return the new intOrderId to us */

AS

/* get the system date automatically, forget about having to use CreateODBCDate() forever! */

DECLARE @dteOrderDate datetime
SET @dteOrderDate = GETDATE()

INSERT INTO tblOrder (vchOrderNote,
			   dteOrderDate,
			    monGrandTotal)
VALUES (@vchOrderNote,
	   @dteOrderDate,
         @monGrandTotal)

/* Use @@IDENITY to return the new primary key value of the order that was just inserted */
SELECT @intOrderId = @@IDENTITY


CREATE PROCEDURE spGS_Upd_Order

@intOrderId int,
@vchOrderNote varchar(20),
@monGrandTotal money

AS

DECLARE @dteOrderDate datetime
SET @dteOrderDate = GETDATE()


UPDATE tblOrder
SET 	vchOrderNote = @vchOrderNote,
	dteOrderDate = @dteOrderDate,
  	monGrandTotal = @monGrandTotal
WHERE intOrderId = @intOrderId


CREATE PROCEDURE spGS_Del_Order

@intOrderId int

AS

/* Alternatively, you may wish to modify tblOrder to include a bit field named
'bolActive' and have spGS_Del_Order update bolActive, setting it to 'false' in the
event of a deletion. This creates a deleted orders archive, allowing an
accidentally deleted order to be recovered. Alternatively, a deleted order could be
inserted into another table and then deleted from tblOrders. These actions are
 beyond the scope of this article but are mentioned here so you are aware of
 alternate possibilities. */

DELETE FROM tblOrder
WHERE intOrderId = @intOrderId

Listing 2:

<cfcomponent displayname="order" hint="order business object">

<cfscript>
//pseudo-constructor
this.DSN = request.dsn;
</cfscript>

<!— note how the output parameter is handled and returned by this method ‡
<cffunction name="appendOrder" access="public" returntype="numeric">
<cfargument name="vchOrderNote" type="string" required="Yes">
<cfargument name="monGrandTotal" type="numeric" required="yes">
<cfstoredproc procedure="spGS_App_Order" datasource="#this.DSN#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@vchOrderNote"
 value="#arguments.vchOrderNote#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_MONEY" dbvarname="@monGrandTotal"
 value="#arguments.monGrandTotal#" null="No">						
<cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" variable="intOrderid"
 dbvarname="@intOrderId" null="No">
</cfstoredproc>
<cfreturn intOrderId>
</cffunction>

<cffunction name="updateOrder" access="public" returntype="void">
<cfargument name="intOrderId" type="numeric" required="Yes">
<cfargument name="vchOrderNote" type="string" required="Yes">
<cfargument name="monGrandTotal" type="numeric" required="yes">
<cfstoredproc procedure="spGS_Upd_Order" datasource="#this.DSN#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@intOrderId"
 value="#arguments.intOrderId#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@vchOrderNote"
 value="#arguments.vchOrderNote#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_MONEY" dbvarname="@monGrandTotal"
 value="#arguments.monGrandTotal#" null="No">
</cfstoredproc>
</cffunction>

<cffunction name="deleteOrder" access="public" returntype="void">
<cfargument name="intOrderId" type="numeric" required="Yes">
<cfstoredproc procedure="spGS_Del_Order" datasource="#this.DSN#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@intOrderId"
 value="#arguments.intOrderId#" null="No">
</cfstoredproc>
</cffunction>

<cffunction name=”fetchOrderList” access=”public” returntype=”query”>
<cfset var RS1 = “”>
<cfstoredproc procedure="spGS_Sel_Order_getAll" datasource="#this.DSN#">
<cfprocresult name="RS1">
</cfstoredproc>
<cfreturn RS1>
</cffunction>

<cffunction name=”fetchOrderByOrderId” access=”public” returntype=”query”>
<cfargument name=”intOrderId” type=”numeric” required=”yes”>
<cfset var RS1 = “”>
<cfstoredproc procedure="spGS_Sel_Order_getByOrderId" datasource="#this.DSN#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@intOrderId"
 value="#arguments.intOrderId#" null="No">
<cfprocresult name="RS1">
</cfstoredproc>
<cfreturn RS1>
</cffunction>

</cfcomponent>

Listing 3:

<cfscript>
//Retrieve a record set of orders, descending by date

//instantiate the order object
objOrder = CreateObject(‘component’, ‘GS.components.order’);

//invoke the fetchOrderList() method
RS1 = objOrder.fetchOrderList();
</cfscript>

<cfdump var=”#RS1#”>


<cfscript>
//Append an Order and retrieve the new order id

//instantiate the order object
objOrder = CreateObject(‘component’, ‘GS.components.order’);

//specify variable name to hold output parameter, invoke appendOrder() method
intOrderId = objOrder.appendOrder(form.vchOrderNote, form.monGrandTotal);
writeOutput(“New Order Id = “ & intOrderId);
</cfscript>

Listing 4:

CREATE  PROCEDURE spGS_Del_Category

@intCategoryId	int

AS

IF EXISTS (SELECT intCategoryId
	  FROM tblProduct
	  WHERE intCategoryId = @intCategoryId)

	BEGIN

/* the string [DBERROR] will be checked for in a CF Function and if it is found, we
know that the error was raised by a stored procedure */

/* the integers at the end of the RAISERROR statement are SEVERITY and STATE
respectively. See SQL Server Books Online for more information on setting these
values. STATE is an arbitrary integer between 1 and 127 that represents information
about the invocation state of the error. The values depicted below are generally
acceptable. The severity of 11 indicates that this is an error that the user can
correct. */

RAISERROR('[DBERROR]This category has products assigned. Remove or re-assign the
products before trying to remove this category. Deletion fails.',11,1)

	END

ELSE
	BEGIN

	DELETE FROM tblCategory
	WHERE intCategoryId = @intCategoryId

	END


Listing 5:

ALTER   PROCEDURE spGS_Utl_ProcessOrder

@nchUUID		char(35),
@intCustomerId		int,
@intResult		int,
@nchAuthCode		char(6),
@monAmtCharged	money,
@monSubTotal		money,
@monTax		money,
@monShipping		money,
@nchCvv2Match		char(1),
@bolMember		bit

AS

/* Purpose: rolls an authenticated order over from staging into non staging tables.
 This proc does not return a record set. It just takes input parameters and based
 on these values, attempts to roll the order over from the shopping cart
 (tblStagingOrderProduct) to the order tables (tblOrder and tblOrderProduct)
 Developer: G Szabo
 Created: 6/1/2004
 */

-- make sure product pricing information isn’t changed in the middle of this rollover
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

DECLARE 	@intOrderId	int,
		@dteOrderDate	datetime,
		@intProductId	int,
		@intQuantity	int,
		@monPricePerUnit money

-- set the order date
SET @dteOrderDate = GETDATE()

-- get the next order ID from tblNextNum (you can use identity columns instead of
 this method if you wish)
EXEC spCMS_Utl_NextNum @intOrderId OUTPUT

-- insert the order header
INSERT INTO tblOrder (	intOrderId	,
			intCustomerId	,
			dteOrderDate	,
			intResult	,
			nchAuthCode	,
			monAmtCharged	,
			monSubTotal	,
			monTax		,
			monShipping	,
			bolShipped	,
			nchCvv2Match	)
VALUES	( 	@intOrderId		,
			@intCustomerId		,
			@dteOrderDate		,
			@intResult		,
			@nchAuthCode		,
			@monAmtCharged	,
			@monSubTotal		,
			@monTax		,
			@monShipping		,
			0			,
			@nchCvv2Match		)

-- insert the order items (children)
DECLARE csrRecord CURSOR LOCAL SCROLL STATIC FOR
SELECT intProductId,
	 intQuantity,
FROM 	 tblStagingOrderProduct
WHERE nchUUID = @nchUUID

OPEN csrRecord
FETCH NEXT FROM csrRecord INTO @intProductId, @intQuantity, @intMKEY

/* this operation will iterate over each item the customer placed in the staging
table (the shopping cart) and insert each row into tblOrderProduct (live order
items table). Alternatively a temp table could be used instead of a cursor. Since
there generally aren’t that many items in a cart, a cursor is being used. */
WHILE @@FETCH_STATUS = 0

	BEGIN
	
	-- if the user is a member, they get a discount, check membership status and
	apply correct price
	IF(@bolMember = 1)
		BEGIN
		SELECT @monPricePerUnit = monMemberPrice
		FROM	 tblProduct
		WHERE intProductId = @intProductId
		END
	ELSE
		BEGIN
		SELECT @monPricePerUnit = monNonMemberPrice
		FROM	 tblProduct
		WHERE intProductId = @intProductId
		END

	-- perform the insert operation
	INSERT INTO tblOrderProduct ( intOrderId	,
	intProductId	,
	intQuantity	,
						bolShipped	,
						monPricePerUnit )
	VALUES ( 				@intOrderId,
						@intProductId,
						@intQuantity,
						0,
						@monPricePerUnit)

	FETCH NEXT FROM csrRecord INTO @intProductId, @intQuantity, @intMKEY

	END

	-- clean up
	CLOSE csrRecord
	DEALLOCATE csrRecord

IF @@ERROR !=0
	BEGIN
	ROLLBACK TRANSACTION
	RAISERROR('[DBERROR] An error occurred in the order rollover transaction.',11,1);
	END
ELSE
	BEGIN
	COMMIT TRANSACTION
	END