I recently attended a ColdFusion certification course. One of the things I learned in the course was the use of Stored Procedures(SP). SP are a great way to move your SQL statements out of your code and allows for code re-use. It also takes the strain off your ColdFusion server and forces your SQL server to crunch all the calculations/numbers (which is what it is intended for). This practice will help speed up your process time for your page.

One of the features of the cfstoredproc command is the cfprocresult command. The cfprocresult returns the resultset of your stored procedure as a query. If you have more than one resultset, the cfprocresult has an attribute to set which resultset you want.

For example, you create a stored procedure for finding the employees who are male or female in your organization. The stored procedure might look something like this:

BEGIN
   SET NOCOUNT ON;

   --Get Males

   SELECT   FirstName, LastName
   FROM   TableEmployee
   WHERE   Gender = 'M'

   --Get Females

   SELECT   FirstName, LastName
   FROM   TableEmployee
   WHERE   Gender = 'F'

   END

Within your code on your page, you can call these two results like this:

<cfstoredproc procedure="spEmployeesByGender" datasource="your_datasource">
      <cfprocresult name="Males">
      <cfprocresult name="Females" resultset="2">
   </cfstoredproc>

There is no need to put a resultset on the first cfprocresult. However, if you only wanted to return the Females in this example, you must include the resultset attribute.

You can also pass variables to your stored procedure using the cfprocparam attribute of the cfstoredproc tag. Here is an example of the cfstoredproc tag with cfprocparam and cfprocresult.

<cfstoredproc procedure="spEmployeesByGender" datasource="your_datasource">
      <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="M">   <!--- Send M for Males --->
      <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="F">   <!--- Send F for Females --->   
      <cfprocresult name="MaleEmployees">
      <cfprocresult name="FemaleEmployees" resultset="2">
   </cfstoredproc>

Now you can define vairables within your stored procedure to pass to your query statements.

@MyMaleGender VARCHAR(1), @MyFemaleGender VARCHAR(1)

   BEGIN
   SET NOCOUNT ON;

   --Get Males

   SELECT   FirstName, LastName
   FROM   TableEmployee
   WHERE   Gender = @MyMaleGender

   --Get Females

   SELECT   FirstName, LastName
   FROM   TableEmployee
   WHERE   Gender = @MyFemaleGender

   END