How to use the cfqueryparam for a null value to get records from a table?

Can i use the cfqueryparam for a null value to get records from a table?

My sample select Query is

Select * from users where lastname is null

Dan Bracuk's edit starts here

Here is a sample query against an oracle database:

<cfquery name="x" datasource="pamot">
select *
from clinic_fu
where hsc_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="fred" null="yes">
</cfquery>

Here is the debugging from the web page.

select *
from clinic_fu
where hsc_number = ?

Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) =

How do you interpret this? Was it this:

where hsc_number is null

or this

where hsc_number = ''

Solution

The way the debug SQL statement would be interpreted is where hsc_number = null which will be false for all records.

In some SQL languages (maybe just MySQL), you can create null safe expressions. The statement would work as both an is null when a null is specified or as an equals statement when a value is specified. I personally dont work with Oracle DB, but from my quick research, the only option for null safe expression would include function calls that could impact performance.

An if statement to switch to is null when you need null records should be the best way to go here.

<cfquery name="x" datasource="pamot">
  select *
  from clinic_fu
  where hsc_number
  <cfif conditionForNull>
    is null
  <cfelse>
    = <cfqueryparam cfsqltype="cf_sql_varchar" value="#hsc_number#" />
  </cfif>
</cfquery>

I find the null attribute useful for inserts and update statements more then with select statements.

<cfquery  result=>
  INSERT INTO users
  (first_name, last_name)
  VALUES
  (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.firstName#" null="#form.firstName eq ""#" />, 
  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lastName#" null="#form.lastName eq ""#" />);
</cfquery>

In this statement, when the name values are empty strings a null value will be passed to the database instead of the form value. It's not a very realistic example, for I would personally write the empty string instead of a null, but it shows how the null attribute can be used.