In the same way that you can create custom tags from fragments of ColdFusion code and then call those custom tags from other ColdFusion templates, you can do virtually the same thing with stored procedures. Stored procedures can call other stored procedures much the same way that they are called from ColdFusion but without the tag-based interface. Say, for example, that you have a stored procedure, sp_GetInventoryItem, that retrieves an InventoryItem row based on the ItemNumber value passed. You use sp_GetInventoryItem throughout your e-commerce application to get product information as users browse the catalog, and you also use it to retrieve inventory items for administrative work. Now you want to incorporate sp_GetInventoryItem with another stored procedure—sp_DiscountItem— that discounts the price of an item based on whether the user enters a valid discount coupon code at the beginning of his shopping session. You can repeat the code to retrieve the inventory item and perform the discount in each stored procedure that requires such a function, or you can make code maintenance easier by breaking out examples of common code into their own procedures and calling them where needed, as shown in the code.

-- This procedure will be called by sp_DiscountItem
   CREATE PROCEDURE sp_GetInventoryItem (
   @giiItemNumber Varchar(15),
   @giiDescription Varchar(40) OUTPUT,
   @giiUnitPrice Numeric(12,2) OUTPUT,
   @giiAvailableToSell Integer OUTPUT,
   @giiComments Varchar(200) OUTPUT
   )
   AS
   SELECT
   @giiDescription = Description,
   @giiUnitPrice = UnitPrice,
   @giiAvailableToSell = AvailableToSell,
   @giiComments = Comments
   FROM
   InventoryItem
 WHERE

ItemNumber = @giiItemNumber
   RETURN
   go
   -- This procedure will also be called by sp_DiscountItem
   CREATE PROCEDURE sp_GetAmountDue (
   @CouponCode Varchar(16),
   @AmountDue Numeric(12,2) OUTPUT
   )
   AS
   DECLARE
   @Redeemed Bit,
   @PercentDiscount Numeric(12,2)
   SELECT
   @Redeemed = Redeemed,
   @PercentDiscount = PercentDiscount
   FROM
   Coupon
   WHERE
   CouponCode = @CouponCode
   IF (@@ROWCOUNT != 1)
   BEGIN
   RETURN -- Coupon doesn’t exist; no price change
   END
   IF (@Redeemed = 1)
   BEGIN
   RETURN -- Coupon already redeemed; no price change
   END
   ELSE
   BEGIN
   SELECT @AmountDue = @AmountDue * (1 - @PercentDiscount / 100)
   END
   RETURN
   go
   -- This is the parent procedure that is called
   CREATE PROCEDURE sp_DiscountItem (
   @diCouponCode Varchar(16),
   @diItemNumber Varchar(15),
   @diDescription Varchar(40) OUTPUT,
   @diUnitPrice Numeric(12,2) OUTPUT,
   @diAvailableToSell Integer OUTPUT,
   @diComments Varchar(200) OUTPUT
   )
   AS
 EXEC sp_GetInventoryItem (

@giiItemNumber = @diItemNumber,
   @giiDescription = @diDescription OUTPUT,
   @giiUnitPrice = @diUnitPrice OUTPUT,
   @giiAvailableToSell = @diAvailableToSell OUTPUT,
   @giiComments = @diComments OUTPUT
   )
   EXEC sp_GetAmountDue (
   @CouponCode = @diCouponCode ,
   @AmountDue = @diUnitPrice OUTPUT
   )
   RETURN
 go

The first time that we ever coded such a thing, our heads nearly split wide open from confusion! Which parameter is the input to which output, and how does this one pass back to that one and so on? Well, we finally figured out a way to explain one procedure by using OUTPUT parameters that call another in a way that makes sense. You find an Adobe Acrobat file named Output Parameter Tracing.pdf, which contains the preceding listing repeated once for each step in the parameter passing process, along with a diagram for each step and an explanation in plain English of what is happening in each step. We follow one of these OUTPUT parameters—the UnitPrice—on its complete journey through all the stored procedures that use it and show how values are acquired and passed between parameters. We highly suggest that you take a few moments to open that document and follow each step of the process. To really leverage stored procedures in your ColdFusion applications, you need to fully understand this complicated topic. We have repeated the plain English description of each step in the following numbered list, but having the diagram at hand as you read them really helps. The process all starts with the procedure sp_DiscountItem that is called from ColdFusion as follows:

1. sp_DiscountItem is the first stored procedure called. The @diUnitPrice output parameter starts off with a NULL value; at this point, @diUnitPrice is just an “empty bucket” to receive a value back from sp_DiscountItem after it finishes executing and then return that value to the client application. OUTPUT tells sp_DiscountItem, “If you do anything to the value of @diUnitPrice during your execution, OUTPUT its new value back into @diUnitPrice so that the new value can be used by whatever application or procedure called you.” If @diUnitPrice didn’t have the OUTPUT qualifier, it would remain NULL after sp_DiscountItem finished executing.

2. @diUnitPrice is the value fed to the @giiItemUnitPrice OUTPUT parameter of the call to sp_GetInventoryItem. Right now, it is still just a NULL value.

3. @giiUnitPrice initializes with the NULL value supplied by @diUnitPrice and is now ready to call sp_GetInventoryItem.

4. sp_GetInventoryItem is now called with @giiUnitPrice as an OUTPUT parameter. It still contains a NULL value. It’s only job at this point is to provide an empty bucket into which sp_GetInventoryItem may place a value.

5. After sp_GetInventoryItem executes, it selects the column value of InventoryItem.UnitPrice into the OUTPUT parameter @giiUnitPrice.

6. @giiUnitPrice now contains the unit price retrieved from the inventory item rather than a NULL value.

7. Because @giiUnitPrice is an OUTPUT parameter in sp_GetInventoryItem, its new value is OUTPUT back into @giiUnitPrice inside sp_DiscountItem, which just called sp_GetInventoryItem.

8. This new value of @giiUnitPrice is further OUTPUT back into the @diUnitPrice parameter that originally fed the NULL value to @giiUnitPrice, because @diUnitPrice has the OUTPUT qualifier in the call to sp_GetInventoryItem. Now @diUnitPrice contains the same value returned from sp_GetInventoryItem.

9. You now have the undiscounted unit price value from the inventory item record stored in @diUnitPrice, and you can feed this value to sp_GetAmountDue for a possible price reduction.

10. @AmountDue is initialized with the value stored in @diUnitPrice and is now ready to call sp_GetAmountDue.

11. sp_GetAmountDue is now called with @AmountDue as an OUTPUT parameter. It currently contains the unit price value returned from the inventory item record.

12. sp_GetAmountDue may or may not modify the value stored in @AmountDue, but if it does, the modified value is restored in @AmountDue.

13. Because @AmountDue is an OUTPUT parameter in sp_GetAmountDue, its new value is OUTPUT back into @AmountDue inside sp_DiscountItem, which just called sp_GetAmountDue.

14. This new value of @AmountDue is further OUTPUT back into the @diUnitPrice parameter that originally fed the unmodified unit price value to @AmountDue, because @diUnitPrice has the OUTPUT qualifier in the call to sp_GetAmountDue. Now @diUnitPrice contains the (possibly) modified unit price value returned from sp_GetAmountDue.

15. Because @diUnitPrice is an OUTPUT parameter in sp_DiscountItem, @diUnitPrice can OUTPUT its final, possibly modified value to the ColdFusion application that called it in the first place.

We know that this process sounds complicated—and it is—but that’s just the way that it goes. Some things in software development are rather complicated, and this is one of them. Go over the PDF document a few times, and it should start to become clear. Better yet, experiment with a couple examples of your own and trudge through the complexity until you get it. All things considered, having one stored procedure call another is much better than executing one stored procedure from ColdFusion, coming back to ColdFusion, running another stored procedure from ColdFusion, and so on. Remember that, every time that you establish a separate connection between ColdFusion Server and your database server, you create overhead and decrease the performance of your ColdFusion application. Encapsulating complex business logic in a single call You can combine Input, Output, and InOut parameters, call multiple stored procedures, and return multiple result sets from a single stored procedure call. If you have a very complicated routine, implement it as multiple stored procedures that are called from a single stored procedure that is, in turn, called by ColdFusion. Always look for ways to encapsulate large amounts of data work in stored procedures. As you engineer your ColdFusion applications in this way, pay close attention to the transactional model that you build around the inserts, updates, and deletes that modify your data. Just as are views, stored procedures are formally defined database objects and, as such, can have user and group privileges granted to them. By denying developers direct access to physical tables and, instead, granting them access only to views for selecting data and stored procedures for modifying data, you eliminate all inadvertent ColdFusion developer mistakes that could corrupt or destroy production data. If you analyze your ColdFusion application in terms of the business processes that it supports, break down those processes into specific tasks and transactions, and encapsulate those transactions into stored procedures, you reduce ColdFusion development down to its purest essential: feeding user data to business transactions. In this article you’ve learned how to create and use stored procedures and views, and also how to understand, configure, and use transactions. Programmers often design and develop databases as if they are single-user systems and discover the flaws of such thinking only after it is too late. Understanding the exact nature of transactions and how to control the isolation between them is crucial to deploying database systems that effectively handle real-world traffic. Stored procedures may seem a little foreign and difficult at first, but learning and using them in your everyday code is well worth your time. By encapsulating complex business logic into single calls that are executed directly on the database server, you not only make code management simpler and easier, but you also gain an important performance enhancement. Stored procedures that return multiple result sets may seem daunting if Oracle is your database server of choice, but after you understand the unique mechanisms that Oracle uses to reference result sets and how ColdFusion interfaces with these mechanisms, you should have no problem implementing them. Combining your new knowledge of transactions and stored procedures helps you produce secure, maintainable code that performs well under heavy multi-user loads. Take a close look at your existing code and you most likely find a number of opportunities where stored procedures can improve the performance and robustness of your applications.