Today I overcame some pretty nutty programming hurdles, so I thought I’d blog about it. Maybe Google will pick up the post and someone might find the info useful.

So I needed to convert some simply queries into stored procedures. Here’s an example of one:

<cfquery name="qryEpisodes" datasource="Podcasts">
USE Podcasts
SELECT *
FROM Podcasts AS A
INNER JOIN Episodes AS B ON A.PodID = B.PodID
WHERE 1 = 1
<cfif IsDefined("ARGUMENTS.PodID") And ARGUMENTS.PodID NEQ "">
  AND A.PodID = #ARGUMENTS.PodID#
</cfif>
<cfif IsDefined("ARGUMENTS.EpID") And ARGUMENTS.EpID NEQ "">
  AND B.EpID = #ARGUMENTS.EpID#
</cfif>
ORDER BY A.PodSortOrder ASC, B.EpDatePublished DESC
</cfquery>

I started here, but that only dealt with some simple stored procedures. Still, very useful info. My query had some conditional statements in it, so I first tried using an IF statement in my Stored Procedure which looked something like this:

CREATE PROCEDURE readEpisode (
  @arg_PodID INT = NULL,
  @arg_EpID INT = NULL
)
AS
BEGIN

  SELECT *
  FROM Podcasts AS A
    INNER JOIN Episodes AS B ON A.PodID = B.PodID
  WHERE
    1 = 1
    IF @arg_PodID IS NOT NULL
      AND A.PodID = @arg_PodID
    IF @arg_EpID IS NOT NULL
      AND B.EpID = @arg_EpID
    ORDER BY A.PodSortOrder ASC, B.EpDatePublished DESC;

END;

I executed the SQL statement and was presented with on of SQL Server’s typical cryptic error messages saying that “something is wrong around ORDER”… about as helpful as saying “something is wrong with this query”.

Eventually I deduced that having the IF embedded in the WHERE clause was the source of my problems. In my searches for a solution I came across this forum post which had some useful info. I found that I could do something like this:

...
WHERE
  1 = 1
  AND A.PodID = ISNULL(@arg_PodID, '%')
  AND B.EpID = ISNULL(@arg_EpID, '%')
...

The ISNULL function first tries to return the value of @arg_PodID, but if it’s null, it returns ‘%’, which is a wildcard that should return everything. Gave that a try — no dice. Since @arg_PodID is an integer, substituting ‘%’ (a string expression) was throwing an error.

The search continued and eventually I found this article which led me to learn about the COALESCE function. Voila! Exactly what I needed. Lets see it in action and then I’ll explain the trick:

...
WHERE
  1 = 1
  AND A.PodID = COALESCE(@arg_PodID, A.PodID)
  AND B.EpID = COALESCE(@arg_EpID, B.EpID)
...

The COALESCE function returns the first non-null value it finds within the set of arguments that you give it. The trick to how this works is that if @arg_PodID is still null (it’s default value), the A.PodID column will be returned. The column will always equal itself, so no records get filtered out. This works perfectly because I only want the recordset filtered by PodID if it gets passed in.

I executed the SQL statement and presto — I had my stored procedure. Now I moved on to converting my cfquery to a cfstoredproc. It started out looking something like this:

<cfstoredproc procedure="readEpisode" datasource="Podcasts">
  <cfprocparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.PodID#" />
  <cfprocparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.EpID#" />
  <cfprocresult name="qryEpisodes" />
</cfstoredproc>

Tried that and got this error: Invalid data for CFSQLTYPE CF_SQL_INTEGER. The problem was that ARGUMENTS.PodID and ARGUMENTS.EpID were both null/empty, so it was choking when it tried to convert null data to an Integer data type.

Ok… so how do I pass them as null when they are actually null? First I thought that maybe I could just conditionally exclude them altogether if they were null, but since there is no way to map a cfprocparam to a specific argument in my stored procedure, I figured it would screw things up if say ARGUMENTS.PodID was omitted while ARGUMENTS.EpID was passed in (EpID would end up getting assigned to the PodID argument within the stored procedure). Apparently there are plans to enable the dbvarname property so that you can map cfprocparams to stored procedure arguments, but at this point it doesn’t work. You can read more about the dbvarname property here.

Ok, stuck again. After some more searching I lucked out and found this article. If you look at the second code example you’ll see where he’s using another property of the cfprocparam tag called null. When set to true, this property will pass a null instead of trying to map the value to a data type. His example even has some magic that shows how you can conditionally mark the null property true or false. Here’s how it looked on my code:

...
<cfprocparam cfsqltype="CF_SQL_INTEGER" null="#Not Len(ARGUMENTS.PodID)#"
  value="#ARGUMENTS.PodID#" />
<cfprocparam cfsqltype="CF_SQL_INTEGER" null="#Not Len(ARGUMENTS.EpID)#"
  value="#ARGUMENTS.EpID#" />
...

Now that’s slick. Checking for Len(ofSomething) is a trick I’ve seen Ray Camden use a lot and the CF7 Web Application Construction Kit book he co-wrote uses it a lot too. If something has length at all, it will return true, otherwise it will return false. So in this example, if ARGUMENTS.PodID does NOT have length, it will return true and thus the null value will be passed.

And that was it! Sadly it took me most of the day to work through those problems, but I learned some new stuff so it was fun.

This entry was posted on Thursday, August 16th, 2007 at 7:58 pm and is filed under ColdFusion. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.