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