I recently came across a stored procedure where the original developer had used a cfif check as a means of conditionally passing in a null value to the stored procedure. While this worked, there is actually a more elegant solution I would like to share.

Example: Conditional Filters

Sometimes I need some arguments in a stored procedure to be optional. To do this I configure the optional arguments to have a default value of null. Take this stored procedure for example.

ALTER PROCEDURE [dbo].[proc_getUsers]
      @userId integer = null
      , @searchName varchar(20) = null
AS
BEGIN
      SELECT 
            u1.userId
            , u1.userName
      FROM 
            users AS u1
      WHERE 
            -- If @userId parameter is not null then filter by @userId
            (@userId IS NULL OR u1.userId = @userId)

            -- If @searchName parameter is not null then filter by LIKE
            AND (@searchName IS NULL OR u1.userName LIKE '%' + @searchName + '%')
END

Now on the ColdFusion side the way I pass in the value or null is dependent on the datatype. Here for example I normally use a value of zero to indicate that I don't want to filter by userId. So if userId is zero I want to pass in null to the stored procedure. Likewise, I want to pass a null if the searchName string is empty.

To do this I simply reverse the boolean evaluation of the userId integer. So as userId > 0 is equivalent to boolean true, I reverse the sign. Thus when userId == 0, I get true. For the search string its only a little more complex. First I get the string length and again reverse the boolean sign of the result.

Both these operations can be performed inline in the cfprocparam thus removing the need for a cfif.

<cfstoredproc datasource="[mydatasource]" procedure="proc_getUsers">
      <!--- Pass in any non zero userId or a null when userId is zero --->
      <cfprocparam 
            cfsqltype="cf_sql_integer" 
            value="#userId#" 
            null="#not(someNumber)#">

      <!--- Pass in any search string or a null when string is empty --->
      <cfprocparam 
            cfsqltype="cf_sql_varchar" 
            value="#searchName#" 
            null="#not(len(searchName))#">

      <cfprocresult name="returnResult">
</cfstoredproc>

Also Possible In CFQueryparams

Although I have not found any place to use it myself this technique can also be applied to cfqueryparams.

<cfquery name="demoQuery" datasource="[mydatasource]">
SELECT 
      u1.userId
            , u1.userName
      FROM 
            users AS u1
WHERE
      userId = <cfqueryparam 
            cfsqltype="cf_sql_integer" 
            value="#someNumber#" 
            null="#not(someNumber)#">
      AND userName LIKE <cfprocparam 
                  cfsqltype="cf_sql_varchar" 
                  value="%#searchName#%" 
                  null="#not(len(searchName))#">
</cfquery>