Sql null behaviour

dev support
1

I’m having issues with null values in a MS-SQL Query. Let’s give a very simple example (cfscript)

var myName = ‘peter’;
var lv.mySQLParams = { pName = { value.myName, cfsqltype=‘varchar’ null=‘#not Len(myName)#’ } }
var lv.q = queryExecute (“SELECT user_id from tUser WHERE user_name=:pName”, lv.mySQLParams);
=> this works and gives me back all records with user_name=‘peter’.

but…
var myName = ‘’;
var lv.mySQLParams = { pName = { value.myName, cfsqltype=‘varchar’ null=‘#not Len(myName)#’ } }
var lv.q = queryExecute (“SELECT user_id from tUser WHERE user_name=:pName”, lv.mySQLParams);
=> this doesn’t work, because pName will be “NULL”. So the query should be translated into “SELECT user_id from tUser WHERE user_name IS NULL”, but it is translated into “SELECT user_id from tUser where user_name=null” (“=null” instead of “IS NULL”)

The problem is the “null” handling in MSSQL. If “null”, it should be “IS NULL” instead of “= NULL”…

Is there a way to get this thing working with null values?

2

There may be better ways, but some conditional SQL should work. Something like:

var myName = "";
var lv.mySQLParams = { pName = { value=myName, cfsqltype="varchar" } };
var lv.sql = "
	SELECT
		user_id
	FROM
		tUser
	WHERE
		user_name #( myName.IsEmpty()? 'IS NULL': '= :pName' )#
";
var lv.q = queryExecute (lv.sql, lv.mySQLParams);