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.
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.
Although I have not found any place to use it myself this technique can also be applied to cfqueryparams.
SELECT
u1.userId
, u1.userName
FROM
users AS u1
WHERE
userId =
AND userName LIKE