I've been developing ColdFusion applications since the early days of version 1.5. While a lot has changed for the better with the product since then, not a lot has changed - unfortunately - in terms of the lousy quality of the CFML code that I'm often asked to review or fix.

Most of the problems I see with CF apps have to do with poorly implemented Transact-SQL (T-SQL) and an overall failure to pull the T-SQL out of the middleware and push it down to the data tier where it really belongs. Whenever I have the opportunity to speak with developers who make this mistake, I often find out that they just plain don't know how to leverage the RDBMS effectively.

Grant: "So Joe, how come you aren't using stored procedures in this code?"

Joe Developer: "Well, I've never learned how to write stored procedures. I know it's the right way to do it but I'm just not sure how to get started."

This article will show you the fundamentals of writing stored procedures and tying them into your ColdFusion MX code. I'll focus on showing you how to do this using an n-tier architecture with ColdFusion MX.

Benefits of Using Stored Procedures
There are several reasons for using stored procedures instead of ad hoc queries. The two main ones relate to security and performance.

As far as security is concerned, using stored procedures instead of ad hoc queries in your CFML middleware ensures a higher level of security in your applications. Stored procedures allow for better data protection because they control how the data is accessed. Ad hoc queries are more susceptible to SQL Script injection attacks. When configuring the data source in the CF Administrator, only allow stored procedures, and make sure never to use the "sa" account to access SQL Server databases (see Figure 1).

With regard to performance, stored procedures generally perform a lot better than ad hoc queries. With CFMX 6.1 I've witnessed ad hoc queries perform roughly 50% better by doing nothing to the T-SQL other than cutting the ad hoc query out of the CFML middleware and pasting it into a new stored procedure. The final step involves replacing the T-SQL inside the <cfquery></cfquery> tag with "exec sp_the_new_proc". There are other ways to improve SQL Server performance beyond simply using stored procedures though. A great Web site CFDJ's own Simon Horwith made me aware of is SQL-Server-Performance.com (www.sql-server-performance.com), and a good book on the topic is Microsoft SQL Server 2000 Performance Optimization Handbook by Ken England.

Are there any downsides to using stored procedures? The most common argument is that by using stored procedures you're marrying your application to the RDBMS. Microsoft is doing some interesting stuff to debunk this using a Provider Model Pattern (see weblogs.asp.net/rhoward/archive/2003/11/07/36393.aspx), though my experience is that the argument is largely true. However, since 1995 I've never had to swap out an RDBMS for any of the hundreds of CF apps that I've written, other than upsizing MS Access to MS SQL Server.

Using n-tier architecture can minimize this blow if it ever happens to you. A lot of energy would be put into rewriting the T-SQL based stored procedures into PL/SQL (if switching from SQL Server to Oracle) but from a CF standpoint, you would just need to make minor adjustments to your business objects. By using a business object tier (read "CF Components"), all of your data access calls occur in your CFCs.

Smoke and Mirrors
For my first few years of developing CF applications I used Microsoft Access exclusively as my back-end database. I've found this to be pretty common among CF developers and indeed many CF developers have never moved beyond this familiar desktop database. The reality is that the majority of CF applications out there aren't of the scope or size that justify the cost of implementing an enterprise class RDBMS such as Microsoft SQL Server or Oracle. So, why tackle the tough stuff?

The reason for doing so is that Access doesn't support stored procedures and the CF/Access developer never learns how to write effective T-SQL. Most of the queries this type of developer writes are of the "SELECT * FROM TABLE WHERE ID=#ID#" variety. Even when such developers move to an enterprise-class database server, the queries continue to be done in-line (ad hoc), with no real improvement in the quality of the T-SQL. When I see this I just want to scream.

When I was learning how to write and call stored procedures from within ColdFusion I quickly learned that it was a lot of smoke and mirrors - it's just not that hard to do once you understand the basics. Perpetuating the smoke and mirrors is the fact that there are many complicated-sounding terms to be concerned about, like Attributes, Parameters, Cursors, @@IDENTITY, and so on. (I'll try to intentionally pepper this article with some of these big college-sounding words in order to demystify them.) Another drawback is that there aren't any CF books that I'm aware of that are dedicated to this topic. So you are obliged instead to glean what information you can from existing titles: Microsoft's decidedly ASP-, ASP.NET-, and ADO.NET-focused resources, and the SQL Server Books Online. It helps to have a friend who can help you out, but if you don't have a special friend, take heart, you can do it…and I'm about to show you how.

First, Get the Tools
If you don't have a copy of Microsoft SQL Server, you should immediately buy the $50 developer edition. Microsoft lowered the price from several hundred dollars to just $50 within the last year; if you didn't know this already, now you have no excuse. Buy it and install it on the workstation or laptop where you do your programming work.

A Microsoft SQL Server purchase (any edition) includes SQL Enterprise Manager and SQL Query Analyzer, two utilities that can be used to write stored procedures. There are other third party products available as well. One free product that I've looked at is Toad for SQL Server (available for free download at www.toadsoft.com/toadsqlserver/toad_sqlserver.htm). My personal preference is SQL Query Analyzer for two main reasons:

1. It's easy to test stored procedures from within SQL Query Analyzer. You right-click on a stored procedure and choose OPEN. A property dialog box opens prompting you to enter all the values for your parameters.
2. In addition to seeing all of your tables listed out alphabetically, it is possible to expand each one (by clicking the "+" symbol next to the table name) so that the column name, datatype, and size can be seen for each attribute. This is very helpful as you will need to know this information when you are writing a stored procedure. See Figure 2.

There are a few other reasons why I like to use SQL Query Analyzer. What's important isn't the tool you ultimately use, but that you are comfortable with the tool you choose, and in my experience SQL Query Analyzer is pretty easy to use. In this article I will use SQL Query Analyzer, though you can use whatever tool you're most comfortable with to accomplish the same tasks.

Clearing the Smoke and Shattering the Mirrors
OK, so now you have the tools you need to begin writing stored procedures. What now? Let's do this using n-tier architecture. This means we do our best to divvy up our code as follows:

1. Data Tier: This is the lowest level of our application. It's where our data lives as well as our API for interacting with our data. Business objects in the Business Objects tier will interact with this API exclusively.
2. Business Object Tier: This is the middle layer of our application. It's where we encapsulate business rules, make calls to our data tier, and create an API for our presentation tier to interact with.
3. Presentation Tier: Otherwise known as the User Interface (UI), this is where we'll write our typical HTML and CFML code.

Step One - Create your Entities (Also Known As Tables)
After you understand your project requirements, begin by creating your tables in SQL Enterprise Manager. All of my tables start with the acronym "tbl" preceding the table name. Read up on SQL Server datatypes using SQL Server Books Online. Books Online is Microsoft's help file system for SQL Server and SQL - an invaluable tool for anyone using the database. You can press the F1 key from within SQL Enterprise Manager or SQL Query Analyzer to easily start Books Online. The most common datatypes that you will likely use are int, char, varchar, and datetime. Use Hungarian notation for your table attributes. Table 1 shows some examples using a table named tblOrder so you can see what Hungarian notation looks like.

Hungarian notation allows you to see the datatype your table attributes are expecting without having to constantly reference the database server. When using CFSTOREDPROC, you'll need to specify the datatype of your stored procedure parameters. If you use Hungarian notation, this job becomes a lot easier.

Please note: if you create this table in MS SQL Server, the intOrderId attribute should be made the primary key and the Identity property should be set to YES. The Identity property is the MS Access equivalent of Autonumber in case you are wondering.

Step Two - Write your Append, Update, and Delete Stored Procedures
Most, if not all, tables in your database will require these three stored procedures at a base minimum. There are code generators available to automate this task, but while you are learning, you should hand-code them. Do try to comment your stored procedures when possible. A single line comment uses two hyphens preceding the comment or, for larger comment blocks, the familiar "/* comment here */" notation is used.

Here is the syntax you will follow when writing a stored procedure with Microsoft SQL Server:

CREATE|ALTER PROCEDURE [OWNER].[PROCEDURE NAME]

[PARAMETER LIST]

AS

[STOREDPROC BODY]

I recommend you not specify the [OWNER] in your stored procedures, but have included it here for completeness.

For all of my stored procedures, I use the following naming convention. You should use something similar so that the functionality of any given stored procedure is readily identifiable just by reading its name:

1. Begin all procs with "sp" for stored procedure.
2. Immediately after the sp, in uppercase, I place the acronym for my application.
3. Underscore, followed by an App for Append, Upd for Update, Del for Delete, Sel for Select, or Utl for Utility, followed by another underscore.
4. The name of the table I am working with, without the "tbl".
5. For Select procs, I generally follow this by another underscore and some form of short description, such as "spGS_Sel_Order_getByOrderId". For Utility procs where more than one table is being accessed, or if I am using a cursor to do some kind of iterative operation, I name the proc as descriptively as possible, such as "spGS_Utl_ProcessOrderByUUID".

Here are simple Append, Update, and Delete procs. To create these procedures, I would type them in just as you see them here, one at a time, and press CTL-E when complete to execute (compile) the stored procedure. Executing compiles the proc and makes it available for future use. If any errors occur during compiling, you'll be alerted and given some clues as to what you did wrong so you can fix the problem. Think about this for a second. You're writing and compiling your database objects in advance. SQL Server won't have to do this at runtime (for example, if you had been using ad hoc queries). This is a good thing. Moreover, you can think of your stored procedures as levers or buttons that you press from ColdFusion when you want some action to occur with your data. Think of your stored procedures as your API (application programming interface) to your data tier - because that's exactly what they are (see Listing 1).

Now we have written the mandatory Append, Update, and Delete procs. How about a SELECT proc to return the list of orders descending on order date:

CREATE PROCEDURE spGS_Sel_Order_getAll

AS

SELECT intOrderId,
	  dteOrderDate,
  vchOrderNote,
  monGrandTotal
FROM	  tblOrder
ORDER BY dteOrderDate DESC

And, one select proc to get a specific order by ID if the user clicks on a specific order in the list:

CREATE PROCEDURE spGS_Sel_Order_getByOrderId

@intOrderId	int

AS

SELECT intOrderId,
	  dteOrderDate,
  vchOrderNote,
  monGrandTotal
FROM	  tblOrder
WHERE intOrderId = @intOrderId	  

Step Three - Write your CFC
You've now completed writing your data-tier interface for tblOrder. It's time to write the business object for this API.

We do this in ColdFusion using CFCs. Start your development IDE of choice (I like CFStudio 5 or Homesite+) and create your CFC. Remember, your business object is the bridge between the presentation layer (the User Interface) and the data tier. Using Web services jargon, your business object will create an API for your UI to consume while at the same time consuming the interface you just created in your stored procedures. I'm not going to show you how to properly architect your CFC here as that is outside the scope of this article. Rather, I'll show you how to call upon the stored procedures we created in Step Two (see Listing 2). Please note that this CFC doesn't use any error handling, which is bad, but - again - that's outside the scope of this article.

There are two important points to remember when using CFSTOREDPROC. First, your CFPROCPARAM tags absolutely must be in the exact same order as your parameters are specified in your stored procedure. Not doing this will create problems and you will often get database exceptions because data types aren't lining up properly. If you get these kinds of exceptions, carefully compare the ordering of your CFPROCPARAM tags and your [PARAMETER LIST] in your stored procedure. Often you will find you missed an attribute completely or just put something in the wrong order.

Second you must select the proper CFSQLTYPE property in your CFPROCPARAM tags. Please consult Table 2 for the CFSQLTYPE to SQL 2000 equivalents. I've included an MS Access column so that you can see the MS SQL Server equivalents for MS Access as well.

It's important to mention that you can execute stored procedures using CFQUERY instead of CFSTOREDPROC. Here's the CFQUERY equivalent to the CFSTOREDPROC code above used in the fetchOrderByOrderId CFFUNCTION:

<CFQUERY name="RS1" datasource="#this.DSN#">
	EXEC spGS_Sel_Order_getByOrderId #arguments.orderId#
</CFQUERY>

Which to use? Unfortunately there's no good rule of thumb and there's very little written on the topic. I've found in some cases that CFQUERY performs better than CFSTOREDPROC. We're talking milliseconds here, folks, not noticeable differences generally speaking. I've also found that CFSTOREDPROC doesn't always work reliably in situations where I have 40 or more input parameters. But, if your stored procedure returns more than one record set, CFSTOREDPROC is your only option. I tend to use CFSTOREDPROC almost exclusively in my code because I find it to be more readable as a developer. CFPROCPARAM specifies my datatypes and whether the parameter direction is IN, OUT, or both. I find it easier to debug CFSTOREDPROC for this reason.

Step Four - Write Your Presentation Layer Code (UI)
Now that you have all of the pieces complete, you can create your presentation layer. Invoke your business object using <CFINVOKE> or using CreateObject() inside a CFSCRIPT block such as you see at Listing 3.

Step Five - Script Your Database
This step isn't essential to getting your stored procedures to work, but it is essential for protecting your time investment in your T-SQL code. Scripting the database is done with SQL Enterprise Manager. This operation will script all of the database objects that you specify into a big T-SQL script that you can then save off in your source code repository. This is really important if you ever need to recreate your database on a new database server. Note that scripting your database doesn't preserve the actual data in the tables. That's what backups are for. To access the Generate SQL Script feature of SQL Enterprise Manager, right-click on a database in the tree view, choose ALL TASKS, and then choose Generate SQL Script. Figure 3 depicts this dialog box.

Error Handling in Your Stored Procedures
With a Delete operation you often need to check to make sure that there will be no orphaned child records after the delete. If there are, generally a warning is displayed to the user and the operation is halted until all of the child records are removed, at which point the user can try deleting again. Without using RAISERROR, this takes two queries, one to check for child records, and another to do the deletion if there aren't any. Using RAISERROR, you can eliminate the extra query and handle it all in a single stored procedure (see Listing 4).

Here's a cffunction I wrote for cleaning up the raiserror string so that it can be displayed nicely to the end user:

<cffunction name="raiseErrorHandler" access="public" returntype="string">
<cfargument name="errorMessage" type="string" required="yes">
<cfscript>
var rVal = "";
if(REFind("\[DBERROR]", arguments.errorMessage, 1)) {
rVal = REReplace(arguments.errorMessage, "\[(Macromedia|SQLServer JDBC
Driver|SQLServer)\]", "" , "ALL");
rVal = REReplace(rVal, "\[DBERROR]", "", "ALL");
}
return rVal;
</cfscript>
</cffunction>

You'll need to wrap a try/catch around your database call (around the cfstoredproc statement) and in the event of a database catch, feed the cfcatch.detail to this function.

Using Transactions
SQL Server 2000 also supports the use of Transactions. A transaction is a single unit of work. Situations that require transactions typically include scenarios where you are moving data from one table to another (and if anything goes wrong the whole job should be voided). If you're using CFTRANSACTION, you don't need to use transactional processing in your stored procedures. When you need to implement a transaction, give some thought as to how you want to do it and how you think it will best perform - in the middleware (e.g., in your CFML) or in the data tier. SQL Server supports several isolation levels and you should check SQL Server Books Online to ensure you are using the correct isolation level. READ COMMITTED is the SQL Server default if you don't specify the transaction isolation level. Here's how you implement a transaction in a stored procedure (this T-SQL code comes after the AS keyword in the stored procedure):

-- set the isolation level if there are any SELECT statements used below, 
otherwise this line isn't needed
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION
	
/* perform data operations HERE that must all be successfully completed 
as a single unit of work */

-- check to see if an error condition was encountered and if so, rollback
IF @@ERROR != 0
	BEGIN
/*ROLLBACK TRANSACTION erases all data modifications made since the 
start of the transaction and frees resources held by the transaction. */
	ROLLBACK TRANSACTION
	END
ELSE
	BEGIN
-- No errors, commit the transaction
COMMIT TRANSACTION
	END

Developer, Know Thy Database
You should now have an understanding of the basics when it comes to implementing stored procedures in your CFMX application. If you have a chance to work with an enterprise-class CF application, spend some time reading the stored procedures and enhancing your knowledge. As your knowledge increases, you'll probably want to spend some time reading about cursors, functions, temporary tables, table variables, and transactions. A SQL Server DBA I once spoke with told me he reads one stored procedure per day from the MS SQL Master database. This is a database that's installed by SQL Server and drives much of the functionality within it. That's a great tip to follow.

Summary
In closing, I hope this article has at least piqued your interest in learning more about stored procedures. The benefits of using stored procedures with an n-tier architecture in your code include:

1. Better performance: Procs are precompiled instead of needing to be compiled at runtime by the SQL Server.
2. With n-tier architecture your code is easier to read and to maintain: No more digging through spaghetti code trying to find a buggy query.
3. Encapsulation of complex data tasks: Stored procedures can perform multiple data operations, one after the other within the same proc. Retrieving the @@IDENTITY after, doing an append operation, as shown in the Append procedure above, is an example of this. Instead of having two <cfquery> operations in your middleware code, one to insert and the next to get the latest ID, you have one stored procedure to handle it all. By using Cursors, it is possible to perform iterations within your stored procedure, and SQL Server 2000 supports Functions as well. These are areas that are worthwhile to read up on to fully exploit the power of stored procedures. (See spGS_Utl_ProcessOrder for an example of using a single stored procedure to perform multiple data operations; see Listing 5). A forthcoming advanced topic article is planned that will discuss spGS_Utl_ProcessOrder in greater detail, as well as get into the finer points of database transactions.
4. Push button capability: Call the proc using the cfstoredproc tag (or cfquery) and it does what it is programmed to do; just like pulling a lever and having your butler show up with your dinner. Well, not exactly, but you get the idea.

Stored procedures are very powerful ways to quickly and efficiently handle data operations. Time spent exploring stored procedures in depth while increasing your knowledge of Transact-SQL is well worth it.

• Figure 1
• Figure 2
• Figure 3
• Source Code
• Table 1
• Table 2