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

Have you ever wanted to concatenate a repeating column as a comma delimitated string in the same query result? Normally, I would use a Scalar Valued Function to COALESCE the values and return them to the calling query (as in the recent post here by Dustin Brisson), but there is a way to do this all in a single request.

SELECT      DISTINCT
         UserID,

         STUFF(
               (SELECT      ',' + SubTableUser.UserRole
               FROM      TableUser AS SubTableUser
               WHERE      SubTableUser.UserID = TableUser.UserID
               FOR XML PATH('')), 1, 1, '') AS UserRole

FROM      TableUser
ORDER BY   UserID

Above is a query with a sub-query. The parent query using DISTINCT against the record values that will remain the same during the repeated result set. The result set in this case happens because there are several users in this fictional database that have more than one user role assigned.

The sub-query, concatenates a comma in front of each additional result for the user role obtained when the user ID of the sub-query matches the distinct user ID of the parent query. This results in a very nice string with an awful leading comma.

You could strip out the comma whatever will display the results, but here we are using the STUFF command to take the first character, in this case a comma, and replace it with nothing. This results in a nice clean list of user roles for each distinct user ID.

If you find this post useful please leave a comment and let me know how you used the information.

Our customers have a process where the lowest level of the organization submits weekly activity statuses up the chain. Each higher level may have 1 or more reporting sub levels and would require them to compile the results before submitting to their superiors. They have, for the longest time, been using email as their means of submission. The reports would come in as either messages in the email itself or Word documents attached to the email. There was no standardization among them. They would take hours cutting and pasting the comments before submitting up the ladder.

So, they came to us asking if their was a better way. We created a web application that displays a standard entry form with categories and text fields for each level to fill out. Once they submit the form, the data is collected in the tables. As the next level logs into the web application, the data from the tables would be compiled and then displayed to the screen.

In order to accomplish this, we use a Scalar-valued Function. The function receives attributes passed from the application and returns the compiled string to display. Here is how it works:

Table
UnitID   Date      CategoryID   CategoryContent   
1   6/13/2011   1      Unit 1 Cat 1
2   6/13/2011   1      Unit 2 Cat 1
3   6/13/2011   1      Unit 3 Cat 1
4   6/13/2011   1      Unit 4 Cat 1
1   6/13/2011   2      Unit 1 Cat 2
2   6/13/2011   2      Unit 2 Cat 2
3   6/13/2011   2      Unit 3 Cat 2
4   6/13/2011   2      Unit 4 Cat 2

Inside your cfquery tag you would include the function call like this

<cfquery name="CompileComments" datasource"YourDataSource">
SELECT DISTINCT
   UnitID, dbo.fnCompileComments('6/13/2011', '1', CategoryID) AS CompiledComments
FROM   TableName
WHERE   Date = '6/13/2011' AND UnitID = 1
</cfquery>

Function name in SQL is dbo.fnCompileComments. List the variables you are passing it and then declare the return Variable with AS.

The SQL function looks like this:

USE [DatabaseName]
GO
/****** Object: UserDefinedFunction [dbo].[fnCompileComments] Script Date: 06/13/2011 13:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnCompileComments]
(
   @MYDate DATE,
   @MYUnitID INT,
   @MYCategoryID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
   DECLARE @Comments VARCHAR(MAX)

SELECT @Comments = COALESCE(@Comments + char(10) + char(13) + '|', + '') + CategoryContent
FROM   TableName
WHERE Dater = @MyDate AND
   UnitID = @MyUnitID AND
CategoryID = @MyCategoryID

RETURN @Comments

END

The expected result:
Unit 1 Cat 1
|Unit 2 Cat 1
|Unit 3 Cat 1
|Unit 4 Cat 1

Unit 1 Cat 2
|Unit 2 Cat 2
|Unit 3 Cat 2
|Unit 4 Cat 2

The user defined function uses the COALESCE function to compile the comments together. I use a char(10) and a char(13) to put a return line and carriage return between each comment. I also include a unique character "|" that I use for a specfic function elsewhere in the application.

Last year, I wrote a File Management application in ColdFusion9. It allows users to manage their uploaded content online through a web Graphical User Interface (GUI). Sometimes, when a user is moving a folder of files, or renaming a folder of files; the SQL will not execute the as expected. This seems to happen when the files are held open by another user.

As a result, the database does not update with the new information, but the ColdFusion manipulates the files and folders anyway. This causes the application to become unstable since it now has rouge files and folders that are not tracked by the database.

To help reduce this risk, I recently implemented a small level of error checking on the SQL statements using @@ROWCOUNT. This has only been tested with MS SQL2008 and ColdFusion9.

<cfquery name="SetFileValue" datasource="DataBaseName">
   SET NOCOUNT ON
   UPDATE   TableFile
   SET   FileValue = 'SomeValue'
   WHERE   FileID = 2

   SELECT ChangeCount = @@ROWCOUNT
   SET NOCOUNT OFF
</cfquery>

This will result in a value being assigned to SetFileValue.ChangeCount and I can check if it is greater than zero, meaning more that one result has occurred in the update query. I could also check with a SetFileValue.RecordCount, but this is making SQL do more work and returning a scoped value instead of a ColdFusion query result value. If you follow. The @@ROWCOUNT method above was aslo used because it can be retrieved now by MS SQL, not just ColdFusion.

Now I can check that the update actually occurred and resulted in at least one record change before performing the next step. In this case, renaming a file from one value to another on the server.

<cfif IsDefined("SetFileValue.ChangeCount") AND SetFileValue.ChangeCount GT 0>
   <cffile action="rename" destination="#FileNew#" source="#FileOld#">
</cfif>

If you find this post useful please leave a comment and let me know how you used the information.

There are usually many different ways to obtain the same results. Not to mention the differences between several right ways and several wrong ways, but that's a post for another day. Consider the following query:

SELECT Record

FROM Table

WHERE RecordID = 1 OR

RecordID = 2

ﾠ

Results:

RECORD
----------
This
That

The table records a RecordID of 1 and 2 for two records named Record (bad names used for an example only) which contain this and that. When queried the results return two records. If you need to return these records in a special order, or parent/child association (RecordID 1 belongs to RecordID 2) you could also consider the following query which will name the two results as two different fields in one horizontal query row.

SELECT SubTable.Record AS SubRecord, Table.Record AS Record

FROM Table AS SubTable INNER JOIN

Table ON SubTable.RecordID = 1 AND Table.RecordID = 2

ﾠ

Results:

SUBRECORD RECORD
---------------------------
Thisﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠ That

If you find this post useful please leave a comment and let me know how you used the information