How to prevent null values for table-valued function parameters?

Question

I have a TSQL Table-Valued Function and it is complex. I want to ensure that one of the parameters cannot be null. Yet, when I specify NOT NULL after my parameter declaration I am presented with SQL errors.

Is it possible to prevent a parameter of a Table-Valued Function to be assigned null by the calling SQL?

Answer 1

In my opinion, it'd be better to check for NULL values at the beginning of your function and use RAISERROR (no, that's not a typo) to raise an exception. EDIT: Unfortunately, this doesn't work for UDFs, so you'll have to go with option 2.

You also have the option of specifying "RETURNS NULL ON NULL INPUT" when you create your function. If this flag is specified, the function will return NULL if any of its inputs are null...kind of paradoxical, but it may be what you want.

From the MSDN CREATE FUNCTION documentation (quoted because they don't have an anchor on their page, bleh):

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Specifies the OnNULLCall attribute of a scalar-valued function. If not specified, CALLED ON NULL INPUT is implied by default. This means that the function body executes even if NULL is passed as an argument.

If RETURNS NULL ON NULL INPUT is specified in a CLR function, it indicates that SQL Server can return NULL when any of the arguments it receives is NULL, without actually invoking the body of the function. If the method of a CLR function specified in already has a custom attribute that indicates RETURNS NULL ON NULL INPUT, but the CREATE FUNCTION statement indicates CALLED ON NULL INPUT, the CREATE FUNCTION statement takes precedence. The OnNULLCall attribute cannot be specified for CLR table-valued functions.

Hope it helps somewhat, and I do agree that it's needlessly confusing.