Stored procedures. Ah, yes. The dark side of database access. You've heard the rumors - faster processing, less network traffic - and have been tempted. But you've also heard those on the easy path whisper about how complex these procedures are...

...and how much of a pain it is to get them to work. So which is right? As with most day-to-day problems - both. However, I'll show you that stored procedures are worth the hassle, and in the end aren't difficult to write - if you're meticulous. Debug? That's a completely different matter. This article focuses on Microsoft SQL Server because Oracle is an even darker path that I dare not tread, and SQL Server has handy tools to help you in your efforts. (The principles, if not the exact code, will hold true no matter which database you use.)

A stored procedure is a batch of SQL statements optimized on creation in the database. When you send a standard SQL statement to the database using CFQUERY, the database first has to determine how best to execute the statement before it actually runs it. The result? Longer processing time. The more complex the statement, the longer it will take. By creating a stored procedure, the code is optimized already.

Why Use Stored Procedures?
The first reason, faster processing time, has already been ad-dressed. The second reason is that when stored procedures are used properly, they result in less network chatter between your Web server and the database (see Listing 1).

Let's assume that the initial query returned 10 rows. That query is one round-trip to the database. The loop results in 10 more round-trips to the database. It's possible to use a SELECT INTO command to do this in one step, but why return the recordset to the Web server at all? Wouldn't it be nice to send the value "Bill" to the database and only get a message back from the database saying the process was complete?

The third reason to use stored procedures is security. In the security settings for SQL Server you can turn off permissions for all commands and allow only stored procedures to access the database. This will keep malicious SQL code submitted in forms and URLs from threatening your database.

Okay, those reasons sound pretty good. So when wouldn't you want to use stored procedures?

• If you use cached queries: They'll execute faster than a stored procedure because the result set is al- ready in the Web server's memory.
• If you plan on porting your application to another brand of database: Stored procedures don't transfer and will have to be rewritten.
• If you have a low tolerance for debugging: The debugging information that stored procedures return is cryptic and usually too vague to be of immediate assistance. (I'll address how to reduce debugging headaches in Part 2 of this article.)

Now that the basics are out of the way, let's look at the syntax for a stored procedure. (Yes, SQL Enter-prise Manager has a stored procedure wizard, but telling prospective employers that you know how to use Microsoft wizards isn't going to get you the big bucks.)

I recommend installing Enterprise Manager and Query Analyzer from the SQL CDs (they're in the client tools option) so you can follow along. Open up Enterprise Manager and drill down into your database to the stored procedure section, shown in Figure 1. Right-click on the right side of the screen and select "New Stored Procedure...". You'll get the screen shown in Figure 2.

Unless you really want to give personal ownership to database objects (not recommended), replace everything in brackets with the name of the procedure you're creating. Enter your SQL code after the "AS" and click "Okay." If your code is syntactically correct, your procedure will be created. If not, you'll get an error message telling you which line number you need to fix.

The end result should look something like Listing 2.

But you don't want every title, of course, just the ones meeting specific criteria, so you need to create some variables. Variables passed into or out of the procedure need to be placed right before the "AS" portion of the procedure. Local variables always begin with "@" and must have a declared data type. If you have a varchar or char data type, you need to include the length of the field in parentheses. Once your variables are declared, you can use them in the procedure (see Listing 3).

Don't put single quotes around local variables or they'll be interpreted as text instead of as variables.

Accessing the Procedure
You now have a useful procedure. How do you get ColdFusion to use it? ColdFusion includes three tags that provide you with access to the procedures.

• <CFSTOREDPROC> works like <CFQUERY> and wraps the other two tags.
• <CFPROCPARAM> is where you provide a list of variables for the procedure to accept and give back.
• <CFPROCRESULT> is the tag to use if you're expecting a result set to be returned, as shown in Listing 4.

The <CFSTOREDPROC> can also take the attribute Debug="Yes|No". If your stored procedure is returning a result set, setting Debug="Yes" will print out the number of rows returned at the bottom of your page. That's all it does. This occurs even if you have debugging disabled in the CFadmin, so I don't recommend using this option in a production environment.

<CFPROCPARAM> needs a TYPE value of "In," "Out," or "InOut." "In" defines values that you're going to give to the procedure. "Out" defines returned values. "InOut" defines values that you provide and then get back. "InOut" is useful only if you want to provide a value and then get back a changed result.

DBVARNAME is supposed to allow you to feed your values into the stored procedure in any order instead of having to list them in the same order as in the stored procedure. This "feature" doesn't work with MSSQL. Even if you use this attribute, the stored procedure still demands that all your values be passed in from <CFSTOREDPROC> as they are found in the procedure. Even so, it can be helpful to use DBVARNAME so you know which CF variables you want associated with which procedure variables.

The <CFPROCRESULT> tag re-quires the name that you want associated with the result set and the number of the result set that you want. In our example we had only one result set so "RESULTSET" is going to be 1. If we had run a second select query, we'd need to refer to that result set as 2.

Timesaving Functions
Apart from the variables you can name using the @ symbol, a few functions (previously called global variables and designated by @@) are available in stored procedures. The most useful are @@Identity, @@Rowcount, and @@Error.

Using SQL2000, @@Identity was the safest way to get the identity value generated by the last INSERT WEO statement. This function is handy when you need to run a second SQL statement to insert the primary key from the first statement into another table. For example, when you create a customer order you may have two tables: Orders contains the customer information and an order number; OrderDetail contains item details. Use the primary key from the Orders table as the foreign key in the OrderDetail table.

When you don't need a variable to be passed either into or out of a procedure, use the DECLARE statement to create a local variable, as in Listing 5.

The @@Identity function will almost always be a safe tool but if you do some fancier processing using triggers, you could end up with the wrong identity. SQL2000 introduced a new function, SCOPE_ IDENTITY, that returns the identity for the current scope only. @@Identity returns the last identity created for the current connection, which may contain triggers and nested stored procedures. So if you're using SQL-2000 I recommend replacing SET @NewIdent = @@Identity with SET @NewIdent = SCOPE_IDENTITY.

The second useful function, @@Rowcount, returns the number of rows affected by the last statement. @@Rowcount is extremely volatile, changing after any statement, even those that return zero rows, so you'll want to transfer the value to a local variable immediately to get accurate results (see Listing 6). Notice that I added the parameter OUTPUT to the @RowsAffected variable. This allows me to use the @RowsAffected value in my CF code after calling the procedure. To make use of this variable I need to include a CFPROCPARAM tag in the procedure call with a type set to "Out." I also need to provide a variable name using the VARIABLE parameter (see Listing 7).

@@Error is the function to use if you want to do some error handling within the stored procedure (not a bad idea). @@Error returns the error code of the most recent SQL statement issued. If the error code equals zero, the code executed without errors. If there's an error, you can use IF, ELSE statements within the stored procedure to fix it or you can return the error to CF for debugging purposes (see Listing 8).

I hope this brief introduction to stored procedures has convinced you that they really can be useful and aren't as difficult to work with as you might imagine. In Part 2 I'll show how to use some of T-SQL's programming constructs such as WHILE loops, IF, ELSE, and string manipulation.

• Figure 1
• Figure 2
• Source Code