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
users AS u1
-- 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 + '%')
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 --->
<!--- Pass in any search string or a null when string is empty --->
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]">
users AS u1
userId = <cfqueryparam
AND userName LIKE <cfprocparam