Using Stored Procedures

Stored procedures are sequences of precompiled SQL statements stored in a database. These SQL statements are later referenced by name and executed at run time. Because stored procedures are precompiled, they run considerably faster than SQL, which is sent from a client application. They are sent via <cfquery>.

In addition to being faster, stored procedures are also more secure than normal SQL statements. Stored procedures can execute SQL on a table that a user does not have access to, and a database administrator can create procedures that hide certain columns of data or do calculations before data is returned.

Stored procedures can be built to accept and return parameters to a ColdFusion template. This means that a query can be dynamic. A stored procedure can also return record sets to ColdFusion in the form of an array or a query.

ColdFusion provides two distinct interfaces for stored procedures, by way of the <cfstoredproc> and <cfquery> tags.

The <cfquery> tag was introduced in Chapter 7, "Using Databases."

NOTE

Most of the major database vendors offer stored procedure support, with different advantages and disadvantages. Stored procedure syntax is different in each type of database; many similarities exist between Microsoft SQL Server and Sybase because those databases were initially the same product. The following stored procedure code, written in Transact-SQL, will run in either Sybase or Microsoft SQL Server:

 Create Procedure spCheckInventory @status varchar(50) As declare @ret int SELECT @ret = ProductRequestedID,productName FROM Inventory WHERE Status = @status return (isNull(@ret,0)) 

The preceding statement needs to be executed only once. From that point on, the stored procedure will be called through the following SQL statement:

 EXECUTE spCheckInventory 'in stock'