NULL parameters in scalar UDFs on MSSQL

Question

The option "RETURNS NULL ON NULL INPUT" for a scalar UDF (see CREATE FUNCTION) stops the function body executing if the parameter is null and simply returns NULL.

That is, it short circuits.

Does anyone know how it handles multiple parameters?

It would be useful to short circuit a function call with multiple parameters, say if the first one is NULL at least.

When I have time, I'll use profiler to try and trace the udf calls. I've searched but can't find anything.. more likely I may not have used the correct search terms.

In the meantime, does anyone have any ideas or experience?

Answers from the other RDBMS worlds are welcome too.. this is an ANSI setting and I saw results for DB2 and MySQL in my searches

Edit, based on comment: For non-CLR functions only

Cheers S

Edit: I don't need to run profiler. Doh! This demonstrates the behaviour:

CREATE FUNCTION dbo.ufnTest (
    @dummy tinyint
)
RETURNS tinyint
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN 1
END
GO
SELECT dbo.ufnTest(0), dbo.ufnTest(NULL)
GO


ALTER FUNCTION dbo.ufnTest (
    @dummy tinyint
)
RETURNS tinyint
--WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN 1
END
GO
SELECT dbo.ufnTest(0), dbo.ufnTest(NULL)
GO


ALTER FUNCTION dbo.ufnTest (
    @dummy tinyint,
    @dummy2 tinyint
)
RETURNS tinyint
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN 1
END
GO
SELECT dbo.ufnTest(0, 2), dbo.ufnTest(NULL, 2), dbo.ufnTest(0, NULL)
GO


ALTER FUNCTION dbo.ufnTest (
    @dummy tinyint,
    @dummy2 tinyint
)
RETURNS tinyint
--WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN 1
END
GO
SELECT dbo.ufnTest(0, 2), dbo.ufnTest(NULL, 2), dbo.ufnTest(0, NULL)
GO

Arrestment

Answer 1

Yes, a function that specifies RETURNS NULL ON NULL INPUT will short-circuit if any of its parameters are NULL.

The documentation does suggest this, although it's unclear and appears to be referring only to CLR functions. (I think this is Microsoft's attempt to clarify that the NULL behaviour specified in CREATE FUNCTION will override the OnNullCall attribute on the CLR method.)

I've tested this with non-CLR functions in both SQL2005 and SQL2008 and it short-circuits exactly as expected.

Hoe

Useful to know. I've got a number of string manipulation UDFs that would benefit from this – Luo

The MSDN documentation should be improved in this case, for sure ! – Argument

Answer 2

Does anyone know how it handles multiple parameters?

is the explanation in your link sufficient, or were you looking for a different take?

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

Luo

@gbn: RETURNS NULL ON NULL INPUT is only applicable for CLR functions. – Product

I know it short circuits for single parameter non-CLR. Where do you get your information? – Arrestment