http://www.sqlservercentral.com/blogs/sqlandme/2012/12/19/sql-server-skipping-function-execution-when-parameters-are-null/

Printed 2014/07/22 05:55PM

SQL Server – Skipping Function Execution when parameters are NULL

By Vishal.Gajjar, 2012/12/19

You can avoid executing a function when NULL values are passed as parameters. To achieve this you need to create function with RETURNS NULL ON NULL INPUT, this option introduced in SQL Server 2005.

You can use this while creating a function as below:

USE [SqlAndMe]

GO

 

CREATE FUNCTION dbo.searchString

(

      @string NVARCHAR(MAX),

      @keyword NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

WITH RETURNS NULL ON NULL INPUT

AS

BEGIN

DECLARE     @retValue NVARCHAR(MAX)

SET         @retValue = 'Input Valid…'

RETURN      @retValue

END

GO

 

Now, when this function is executed and any of the parameters are NULL, it will simple return NULL without executing the function body.

You can test this functionality using below statement:

USE [SqlAndMe]

GO

 

SELECT dbo.searchString(N'SqlAndMe', N'Test'),

SELECT dbo.searchString(N'SqlAndMe', N''),

SELECT dbo.searchString(N'SqlAndMe', NULL),

SELECT dbo.searchString(NULL, N'Test')

GO

 

Result Set:

Input Valid…

 

(1 row(s) affected)

 

 

Input Valid…

 

(1 row(s) affected)

 

 

NULL

 

(1 row(s) affected)

 

 

NULL

 

(1 row(s) affected)

 

As you can see from the last two results a NULL is returned.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe


Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.