Macromedia ColdFusion MX is great for building dynamic Web sites and providing Web-based access to corporate data. Using the powerful and easy-to-use ColdFusion Markup Language (CFML), Web developers can rapidly build and deploy sophisticated Web applications with a variety of database back ends.

In a previous article, I illustrated this flexibility by showing you how simple it is to use ColdFusion to query a SQL Server 2000 database and return data. I also showed how to quickly build add, edit, and delete interfaces to your database. I’d like to build on that by introducing you to some techniques for leveraging SQL Server 2000 stored procedures in your ColdFusion applications.

What are stored procedures?
Stored procedures are precompiled SQL statements that execute within the SQL Server engine. Using them, instead of sending a raw SQL statement to SQL Server, you can call a procedure that has already been defined. This has several benefits over executing standard SQL queries, including:

  • ·        Faster execution, because the statement is precompiled and optimized.
  • ·        Type safety, because all input and output parameters and their data types are strictly defined.
  • ·        Better security, because SQL injection and manipulation hacks are virtually eliminated.
  • ·        The ability to return multiple result sets from a single database trip.
  • ·        A powerful layer of business logic with the ability for one procedure to call additional procedures, handle transactions, and more.

SQL Server 2000 stored procedures are written in Transact-SQL (T-SQL), which contains many built-in features and functions and is a full procedural language in its own right. The examples I’ll present here use relatively simple T-SQL code, but if you use SQL Server 2000 as your RDBMS, you would be well served to become skilled at T-SQL.

Querying the pubs database
The examples I’ll cover were all created in the pubs database, one of the sample databases installed with SQL Server 2000. I’ll start off with the most basic stored procedure possible, a simple select statement. This is the T-SQL code:
FROM authors

To call this stored procedure from ColdFusion, the <cfstoredproc> tag is used. Use this CFML to call the procedure and dump the result set:
<!— Simple select procedure. —>
<cfstoredproc procedure=”getAuthors” datasource=”pubs”>
<cfprocresult name=”result” resultset=”1″>
<cfdump var=”#result#”>

While this works, it doesn’t provide much real benefit over the <cfquery> tag, except for slightly faster execution time. One advantage of stored procedures is the ability to return multiple result sets. A T-SQL stored procedure to do this could be:
CREATE PROCEDURE getAllTitlesAndAllPublishers AS
SELECT * FROM titles
SELECT * FROM publishers

The CFML you’d use to call this procedure and dump both appears in Listing A. You can call a procedure and have it return any number of result sets to you.

Working with procedure parameters
The advantages of stored procedures become more obvious when we move away from unfiltered select statements and introduce procedure parameters. The T-SQL for a procedure that takes an author ID as input and returns that author’s record from the database is as follows:
@au_id varchar(11)
FROM authors
WHERE au_id = @au_id

And the ColdFusion code to call it appears in Listing B.

You can, of course, call procedures with multiple parameters. You can also use database commands beyond SELECT. The next example uses multiple parameters to perform an insert into the database, and if the insert was successful, it returns the au_id of the newly inserted record. Check out the T-SQL code in Listing C. The CFML to call this procedure appears in Listing D.

This procedure call uses both input and output parameters (marked with the keyword OUTPUT) in the T-SQL code. Clearly, the input values are the values we want to insert, and the output value is the newly inserted author ID. Returning the new record’s ID is even more useful when your table uses an auto-incrementing primary key. Instead of inserting the record and then querying to get the new record’s ID, you can do both in a single step.

These stored procedures execute more quickly than a <cfquery>, and you reap security benefits from this approach.

Stored procedures vs. <cfquery>
Aside from performance, the other benefit that stored procedures offers over the <cfquery> tag is increased security. Consider this query:
<cfquery name=”getAuthor” datasource=”pubs”>
SELECT * FROM authors
WHERE au_id = #url.au_id#

The problem here might not be immediately recognizable, but it is very serious. If a malicious user alters the value of au_id in the URL, he or she can wreak havoc on your database. If au_id were changed from 123-45-6789 to, say, 123-45-6789;delete * from authors, some database engines would execute the inserted SQL statement and delete all rows from your authors table. There are ways to defeat this inside your <cfquery> tag, but using stored procedures makes this attack impossible.

At a higher level, you can set ColdFusion and SQL Server to only allow stored procedures to execute, and you can deny execution of standard SELECT, INSERT, UPDATE, and DELETE queries. This way, even if someone were to hack your ColdFusion server and gain command line access, he or she still could not execute any arbitrary SQL statements. The hacker could run only the stored procedures that you have allowed. SQL Server lets you to apply permissions to each stored procedure, which further increases your control over which SQL commands can be executed against your database.

Stored procedures can also limit the amount of work you do as a developer and reduce the amount of code you duplicate. If all of your queries and business logic are in your ColdFusion application, you’re limiting the ways that users can get to your data. If you instead wrap up your database code in stored procedures, your Web application will reap the performance and security benefits, and other client-server applications, such as Visual Basic apps, can reuse your business logic code. Moving your business logic out of your Web application and onto the database is worthy of consideration.

Too much work?

Under what circumstances is the extra work involved in implementing business logic in a database as stored procedures worthwhile, and when is it not? Share your thoughts in our discussion below.

T-SQL is a powerful procedural language, and it allows you to encapsulate complex and sophisticated business logic into your SQL Server databases. ColdFusion MX makes it very easy to leverage stored procedures in your Web applications. This article has shown you some of the advantages of using stored procedures in your own CFMX apps.