title: SQL Server -- Handling null input in CLR User-Defined Function (UDF) with OnNullCall
body: <p>I have a user-defined function in SQL Server (written in .NET) that cleans text.  I'm wondering how to handle null input.</p>
  
  <p>Here is the function in C#:</p>
  
  <pre><code>[Microsoft.SqlServer.Server.SqlFunction]
  public static SqlChars cleanEstActText(SqlChars input)
  {
      SqlChars cascadingSqlChar = removeNBSP(input);
      cascadingSqlChar = optimizeFontTags(cascadingSqlChar);
  
      return cascadingSqlChar;
  }
  </code></pre>
  
  <p>This is the error in SQL if the function gets any null data:</p>
  
  <pre><code>A .NET Framework error occurred during execution of user-defined routine or aggregate "removeNBSP": 
  System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
  System.Data.SqlTypes.SqlNullValueException: 
     at System.Data.SqlTypes.SqlChars.get_Value()
     at UserDefinedFunctions.removeNBSP(SqlChars input)
  </code></pre>
  
  <p>Reading on SO and Google led me to the OnNullCall attribute, which looks promising.</p>
  
  <p>From <a href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmethodattribute.onnullcall.aspx" rel="nofollow">MSDN</a>:</p>
  
  <blockquote>
    <p>true if the method is called when null (Nothing in Visual Basic) input arguments are specified in the method invocation; false if the method returns a null (Nothing in Visual Basic) value when any of its input parameters are null (Nothing in Visual Basic).</p>
  </blockquote>
  
  <p>Sounds exactly like what I want; if I get null, just pass null through.  I'm not quite sure how to implement it, so I check MSDN again (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmethodattribute.aspx), and rewrite the first line of my function from</p>
  
  <pre><code>[Microsoft.SqlServer.Server.SqlFunction]
  </code></pre>
  
  <p>to</p>
  
  <pre><code>[Microsoft.SqlServer.Server.SqlMethod(OnNullCall = false, IsMutator = false, InvokeIfReceiverIsNull = false)]
  </code></pre>
  
  <p>If I do this, I get an error in SQL any time I use it:</p>
  
  <pre><code>Cannot find either column "dbo" or the user-defined function or aggregate "dbo.cleanEstActText", or the name is ambiguous.
  </code></pre>
  
  <p>Am I implementing OnNullCall incorrectly?  Should I be doing something else?  Is there really any good way to make my function pass nulls through?</p>