Null Values within SQL Select Statement with CFQUERYPARAM

I was working on code where I wanted to store nulls into the database and query based on those nulls.

<cfquery name="qRetrieve" datasource="#getDSN().getName()#">
 select semesterID,Major_ID,Campus_ID,AdmTypeReqID,startDate,startOrEndFlag
 from isFull
 where 
  semesterID = <cfqueryparam value="#arguments.DTO.getsemesterID()#" cfsqltype="CF_SQL_INTEGER">
  AND Campus_ID = <cfqueryparam value="#arguments.DTO.getCampus_ID()#" cfsqltype="CF_SQL_INTEGER">
  AND startOrEndFlag = <cfqueryparam value="#arguments.DTO.getstartOrEndFlag()#" cfsqltype="CF_SQL_INTEGER">
  AND AdmTypeReqID= <cfqueryparam value="#arguments.DTO.getAdmTypeReqID()#" cfsqltype="CF_SQL_INTEGER" null="#iif(arguments.DTO.getAdmTypeReqID(),true,false)#">
  
</cfquery>

Notice the last line is using the function IIF instide the null attribute of cfqueryparam.  In the SQL Insert and Update statements, this just works.  I am noticing in interacting with MSSQL 2005 this is not working.  I am getting zero rows back when it should be in the 5-10 rows. For now I have replaced my last line of code with the following 5 rows of code

AND 
   <cfif arguments.dto.getAdmTypeReqID() EQ "">
   AdmTypeReqID is null
   <cfelse>
   AdmTypeReqID = <cfqueryparam value="#arguments.DTO.getAdmTypeReqID()#" cfsqltype="CF_SQL_INTEGER" >
   </cfif>

Has anyone else ran into this?

Comments

  1. joshuamilNovember 1, 2011 at 2:54 PM

    Is it possible that "arguments.DTO.getAdmTypeReqID()" is returning a numeric value 0 instead of a blank value? That IIF() also looks backward to me. If arguments.DTO.getAdmTypeReqID() evaluates to true, then null="true" - is that correct? I would expect it to say: #IIF(arguments.DTO.getAdmTypeReqID(),false,true)# meaning "if arguments.DTO.getAdmTypeReqID() is true, then 'not null' ". Maybe I'm just not understanding the intent.

    ReplyDelete
  2. JFNovember 1, 2011 at 3:11 PM

    That's expected behavior under The SQL-92 standard.

    NULL is a special value reprenting the unknown, it's not equal to anything as you don't know what it is... we all are tempted to say but it's NULL, but the power that be don't agree ;)

    You can say {column} is unknown this can evaluate to a logical condition

    but you can't say {column} = unknown this will always be false as you compare known with unknown.

    You can override this behavior with the SET ANSI_NULLS {ON | OFF} instruction but I wouldn't recommend it.

    http://msdn.microsoft.com/en-us/library/aa259229%28v=sql.80%29.aspx

    ReplyDelete
  3. UnknownNovember 1, 2011 at 3:47 PM

    +1 on what JF said above.

    ReplyDelete
  4. Dan G. Switzer, IINovember 1, 2011 at 4:05 PM

    There's no need to use iif()--which has some performance issues. Just do:

    <cfqueryparam value="#arguments.DTO.getAdmTypeReqID()#" cfsqltype="CF_SQL_INTEGER" null="#(arguments.DTO.getAdmTypeReqID())#">

    ReplyDelete
  5. UnknownNovember 1, 2011 at 6:14 PM

    This comment has been removed by the author.

    ReplyDelete
  6. UnknownNovember 1, 2011 at 6:18 PM

    Why dont you do something like this:


    <cfqueryparam value="#arguments.DTO.getAdmTypeReqID()#" cfsqltype="CF_SQL_INTEGER" null="#NOT isNumeric(arguments.DTO.getAdmTypeReqID())#">

    ReplyDelete
  7. Craig BennerNovember 2, 2011 at 7:26 AM

    Thanks for all the great comments, I would like to clear one thing up, I actually mis-typed the example. I was actually comparing it to blank "" in the null attribute, so the last example is probably the best solution which i probably switch my code too. Other than that, I believe JF is right, it is just frustrating that we can use the same concept in SELECT statements as we do in UPDATE,INSERT,DELETE.

    Thanks everyone for great feedback, sometimes I wonder if anyone watches my blog :-D (PS. thanks coldfusionblogger.org for aggregating)

    ReplyDelete
  8. 21cssIndiaMarch 18, 2014 at 3:54 AM

    Talend Online Training
    Introduction
    Introduction to Talend
    Why Talend?
    Talend Editions and Features
    Talend Data Integration Overview
    Talend Environment
    Talend Environment - Overview
    Repository and Pallate
    Talend Design and Views
    21st Century providing Online training and support on All Technologies. If you are seeking training and support you can reach me on 91-7386622889. Online training by real time Experts. Call us 001-309-200-3848 for online training
    http://www.21cssindia.com/courses/talend-online-training-160.html

    ReplyDelete

Post a Comment