While I was going through a review of a sql server function, it is observed there are many checks implemented if any of input parameter is null then return null. Then, it got strike on mind about “WITH RETURNS NULL ON NULL INPUT” in SQL Server. This is available in SQL server from 2005 version, however, I have not seen people used it efficiently. So thought of sharing about this intelligent way of handling such situations.

Let us start with a sample function as below:

Mighty Sample function

function using WITH RETURNS NULL ON NULL INPUT

The above is a sample function that we can see in many places to check the null param check. There is an option introduced in SQL Server 2005 “WITH RETURNS NULL ON NULL INPUT” to handle this with a grace as below. This way, if any of your input parameters is passed as NULL value, then the function will immediately return NULL value without actually invoking the body of the function. That clears the theory and its time for us to check with sample code as below.

Sample output

The below screenshot explains that the option returns without getting into execution of the function whenever one of the parameter is having NULL value. (Please note I used “Wrong value” to assign to the output variable to understand it actually returns in the beginning or only after the execution.)

If we have not used this technique so far, start using this one, it helps to avoid lots of junk codes in the definition that makes it clean. In addition, it has also observed a small factor of performance improvement for the function.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!