Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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>

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub