Did you know about the NULL attribute of CFQUERYPARAM? I didn't, until today.

Note: This blog post is from 2007. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.

But what would happen if that column in the database was designed to use NULL..but the variable used in the VALUE attribute was an empty string? It would be inappropriate for the insert or update to use an empty string when it should be a NULL.

How would you solve this? Many would just use an IF test to say, in effect, "if it's null, use a null, otherwise use the variable".

But this CFQUERYPARAM NULL attribute is just for this purpose: it accepts a boolean (true/false) to indicate whether and when to use a NULL rather than the VALUE. And rather than a simple yes/no (or true/false, 1/0, on/off, etc) you would more likely use some sort of conditional expression which EVALUATES to a boolean true/false. It could test if the variable used in the VALUE is empty.

An example of when it could make sense

So if an incoming form field like form.department could be empty, you could use:

which would pass in the form.department value--UNLESS it was an empty string, in which case it would pass in a NULL.

Again, this only makes sense when it's important to be using a NULL, such as on an insert or update.

It would NOT likely make sense in a WHERE clause

That said, note that it would NOT be appropriate if this cfqueryparam was being used in a WHERE clause. In that case, the SQL syntax of a WHERE clause equality test ("=") would NOT be appropriate in most DBs if this resolved to = NULL. Instead, the syntax for most DBs is to use "is" rather than "=". To be clear, this null attribute does NOTHING to help there. In fact, you could get an error that would leave you confused if you DID try to use it with a WHERE clause and the NULL attribute was true.

One last gotcha: the variable in VALUE is evaluated regardless

One last gotcha: while the docs say that if the NULL attribute is true, then the VALUE is "ignored", that's loose wording. It means it's not PASSED to the db as the VALUE, which is true. But it does NOT mean that CF "doesn't even pay attention to the variable named".

This has caused people trouble when they assumed they could name in the VALUE attribute a variable which might NOT exist: they assume that testing for it in the NULL should take precedence. That's just NOT how it works. CF WILL try to evaluate the variable in the VALUE attribute, whether this NULL is true or false. Forewarned is forearmed.

You'll find people using various means to test for the existence (isdefined, structkeyexists, etc.), whether testing and assigning some alternative result BEFORE this line of code, or perhaps trying WITHIN this line of code (using iif, de, and other expressions).

Update as of CF2016:

As of CF2016, one can use the "safe navigation" feature which tells CF to resolve to as an empty string if a variable doesn't exist. So one could write the code above like this:

The logic there is that if the form.department field does not exist, then the VALUE will literally resolve to an empty string, and then for the NULL attribute, the same empty string WILL match the test of whether it IS an emptystring--which will be true, which means the NULL will be true and CF will pass in a NULL to the database.

It can be a lot to keep straight in your head. Try some code to check things out: but again, beware that if you use this in a WHERE clause, then the NULL being correctly passed in WILL lead to a syntax error if you're passing that to a SQL comparison like = or <>. You often need alternative SQL for dealing with NULLs--and that's NOT what this NULL attribute is for. It's for controlling simply whether CF should SEND A NULL for the VALUE!

The null attribute for cfqueryparam is not new

Finally, it may be worth nothing that this NULL attribute for cfqueryparam is not new. It's been around since 4.5 according to the CFML language history file. I'd just never noticed it before. I learned about it today from a couple of folks on the great CFAUSSIE list.

Now, to be honest, the docs (CFML Reference for the tag) just don't make it as clear as it could be, I don't think (otherwise I'd like to think I'd have noticed it before). I don't see any mention of it in the Developer's Guide, for instance.

Anyway, you can read more about it in a blog entry from Michael Sharman, who I see also just happened to blog about it last month, with a lot more detail:

http://www.chapter31.com/2007/02/04/cfqueryparam-and-conditional-handling-of-nulls/

You might also want to read the comments there as well as at the CFMX 7 docs for the tag, both of which have people sharing their experiences using the tag, over time.

For more content like this from Charlie Arehart:

• Signup to get his blog posts by email:
• Follow his blog RSS feed
• View the rest of his blog posts
• View his blog posts on the Adobe CF portal

Need more help with problems?

• If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
• See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed