Testing for Nothing Parameters

This might be a bit of a niche issue, specific only to odd systems which replace parameters out of a sql string directly before it's submitted to the database engine but I recently had to test to see if something set up in this way was pulling back a null... sadly when the system had nothing to replace the parameter with that's exactly what it did, which caused havoc within the query as a whole.

The Problem

Here's a quick example, if we take the below query and assume that personID 0 is a default user of the system and we're trying to get a PersonID to allocate work to:

select *
from Person
where personid in (0, $Param(ThisPersonID))

If assume that the $Param(ThisPersonID) will be replaced where the parameter ThisPersonID exists in the process after checking the database, then we need to be able to ensure that the following queries will work as if the user is not found on the database we will allocate to the default person.

select *
from Person
where personid in (0, 512)

select *
from Person
where personid in (0, )

Consideration

  you may have noticed that one of the major issues here is that the parameter replacement won't replace with the word NULL where a value doesn't exists, if it did then a simple isnull would do the job and we could move on but how do you insulate against an absence in code?

Solution - nullif(

 So whilst addressing this issue recently I came across the nullif function, which is what makes this (potentially slightly hacky) solution possible.

nullif ( expression , expression)

 This function will return the first expression if both of the passed expressions differ and if they are the same it will return null - which is useful! that being the case we can use this within an isnull function to get what we need:

isnull( nullif( '$Param(ThisPersonID)', '') , '0')

This will mean that either the held ThisPersonID parameter is passed back and if there isn't one then the nullif function will return a null and the insull function will in turn interpret that and return the secont parameter that it has - success!. You may have noticed that we're using strings to do our comparison, that's because an empty space in sql is just an empty space so trying to compare as integers wouldn't work at all - obviously if we're using this as part of a where condition testing integers however we need to convert the output as the above will output a string, fortunately this is all very easy to do with a standard cast or convert:

cast(isnull(nullif('$Param(ThisPersonID)',''), '0') as Int)

 Which gives us a final and fully insulated query of:

select *
from Person
where personid in (0, cast(isnull(nullif('$Param(ThisPersonID)',''), '0') as Int))

which seems like a long walk for a short glass of water but hey, it does the job!.