Why should ColdFusion analyze the query results when the database could do it and you could save the bandwidth for something that really needs it?

In Part 1 of this article (CFDJ, Vol. 3, issue 10) I introduced stored procedures and performed some basic tasks with them. In this article I show how to do more complex tasks that involve IF/ELSE statements, looping, and string manipulation.

First, see Listing 1; how often have you had to do something similar? Let's say you just received a note from your product supplier stating that Item 20 has a new, lower everyday price of $15. Now your store is running a special in which you give away certain items, but you can't remember if Item 20 is one of those items. You could either look up the item and its cost and then decide whether to send another request to the database to update the price, or simply tell the database which item you want to edit, its new cost, and let the database do everything else.

To process this request the database needs two pieces of information: the item number and the price. The ColdFusion end of the procedure looks like Listing 2.

In the database we need to say what the procedure will be called (I begin all my procedures with "pr_" to identify them as stored procedures. It may seem to make more sense to use "sp_", but "sp_" is used for system stored procedures that can be accessed from any database on the server.)

CREATE PROCEDURE [pr_ItemCost]

We add our variables:

@ItemNo [int],
@Cost [money]

AS

Then we write the SQL statement. First we need the current value for cost because if it's zero, we aren't going to change the amount. If we only want to know if the cost is zero but don't care what it is if it's not zero, we can use the @@ROWCOUNT function instead of actually setting local variables and doing comparisons. (@@ROWCOUNT is a function that tells how many rows were affected by the previous statement.)

SELECT Cost
FROM Items
WHERE ItemNo = @ItemNo
AND Cost = 0

IF @@ROWCOUNT = 0
BEGIN
UPDATE Items
SET Cost = @Cost
WHERE ItemNo = @ItemNo
END

Let's look at this code. In the first select statement we determined if Item 20 had a cost of zero. By querying for the item with a cost of zero we could get two results: one row or zero rows. @@ROWCOUNT tells us how many were returned. If @@ROWCOUNT=0, then Item 20 has a cost that's not zero.

Our IF statement requires a BEGIN and an END command to let the database know where to stop the execution of the IF statement. In between we put our update statement that will change the price of the item only if its original price wasn't zero.

What if we do care what the cost currently is? Maybe we have a company policy that items under $10 will only increase in cost by $3 or the new cost, whichever is less, but everything else that isn't free increases to whatever the new cost is. Normally, you would change your CF code to reflect the business rules, but with stored procedures you can keep the two separate. This will keep your Web developers happy because they probably enjoy making new site features more than getting into the minutia of business rules.

The procedure starts the same but we'll need a local variable within the procedure to hold the current cost of the item:

CREATE PROCEDURE [pr_ItemCost]

Then we need to add our variables:

@ItemNo [int],
@Cost [money]

AS

DECLARE
@CurrentCost [money]
Next, we need to set this new variable equal
to the current value using a special select statement:

SELECT
@CurrentCost = Cost
FROM Items
WHERE ItemNo = @ItemNo

Finally, we need to use our business rules to decide if the cost should change:

IF @CurrentCost = 0
BEGIN
. . .
END
ELSE IF @CurrentCost < 10
BEGIN
. . .
END
ELSE
BEGIN
. . .
END

Notice that each piece of the IF/ ELSE statement requires its own BEGIN and END. You can include as many IF/ELSE statements as you need, and you don't need to include a final ELSE clause, just as in ColdFusion.

IF/ELSE statements are useful but what's really helpful is the ability to replace some of your CFLOOPs over recordsets with a single stored procedure, saving you many trips to and from the database.

Let's assume that the items in our store can be assigned to various special occasions. One way to do this is to have a multiple select-box form submitted to a template that would loop over the list, adding a row to the database each time. If you have the following list of events: birthday, wedding, Mother's Day, anniversary, and graduation, you could have up to five separate calls to the database. Instead, let's pass the entire list to the database and let the database do the looping.

Looping in SQL consists of using WHILE instead of LOOP. This is similar to using conditional CFLOOPs.

We want to loop over the list, add each value to the database, then remove it from the list until the list is empty. This will require some string manipulation as well as looping. This complete procedure can be seen in Listing 3.

CREATE PROCEDURE [pr_AddItemEvents]

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

AS

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

@ListLength will hold the character count of our list. @ CommaCount holds the position of the first comma in our list.

SET
@ListLength =
Len(@EventList)

Len() in SQL works the same as in CF

WHILE @ListLength >0
BEGIN
SET
@CommaCount = CHARINDEX(',', @EventList)

CHARINDEX tells us at what character count the first match for the pattern occurs.

We need to know this so we can properly extract our values from the list. Use the function PATINDEX if you need to match strings with wild cards (*, %).

IF @CommaCount > 0
BEGIN
SET
@EventValue =
SubString(@EventList, 1, @CommaCount-1)
END

@Event Value is the current value in the list. If there's a comma in the list we need to take all the characters starting at 1 and ending right before the comma, which is why we use @CommaCount -1.

ELSE
BEGIN
SET @EventValue = @EventList
END

If there aren't any commas we can set @EventValue equal to whatever is left in the list.

INSERT INTO Event_Items_Xref
(Item_Fk,
Event_Fk)
VALUES
(@ItemNo,
@EventValue)

If we were doing the looping on the CF server instead of in the database, we would repeatedly have to call this query through ColdFusion.

IF @CommaCount > 0
BEGIN
SET
@EventList =
Right(@EventList, @ListLength-@CommaCount)
END

If we have commas in the list we need to remove the current item and the trailing comma from the list. The Right function works the same way in ColdFusion as in SQL.

ELSE
BEGIN
SET @EventList = ''
END

If there aren't any commas in the list, the list is at its last value so we can remove the last value from the list.

SET @ListLength = Len(@EventList)
END

We need to reset the list length after each loop so that the WHILE statement can check the current length. When the length equals zero, the loop stops running.

This article presented several useful programming constructs that will save your applications from multiple trips to the database. However, stored procedures can do much more. For example, stored procedures have access to SQL objects called cursors. A cursor is a result set that you can loop over one row at a time - similar to using CFOUTPUT with a query. This is handy for shopping carts in which each line item needs processing as it's added to an order.

There are also several special stored procedures that allow you to use the command line, similar to CFEXECUTE, and registry editing commands, similar to CFREGISTRY. Finally, as with ColdFusion, you can create special C++ objects that can be accessed through stored procedures, making it possible to move complex programming off the Web server and into the database.

If you want to find out more about MS stored procedure programming, I recommend the book SQL Server 2000 Stored Procedure Programming by Dejan Sunderic and Tom Woodhead (Osborne Press). This book covers all the basics as well as more advanced topics including OLE and XML integration. I also recommend opening up the SQL Server Books Online (www.microsoft.com/sql/techinfo/productdoc/2000/books.asp) every time you have a question about syntax or possible functions. They're a very thorough reference and better than any SQL reference book you can buy.

• Source Code