Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012 > SQL Server – Skipping Function Execution when parameters are NULL

SQL Server – Skipping Function Execution when parameters are NULL


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

  1. Ben
    23.12.2012 04:05 | #1

    thanks Vishal for writing this great piece of content. It helped me a lot as I am a beginner and I was having problems with this.

  2. Alok
    28.12.2012 04:21 | #2

    Can this only happen with Table Valued Function or with Scaler Function too. Becuase I am trying to implement this change in a Table Valued Function and it is giving me syntex error :

    An invalid option was specified for the statement “CREATE/ALTER FUNCTION”.

  3. Alok
    28.12.2012 04:22 | #3

    Alok :Can this only happen with Scaler Function or Table Valued Function too. Becuase I am trying to implement this change in a Table Valued Function and it is giving me syntex error :
    An invalid option was specified for the statement “CREATE/ALTER FUNCTION”.

  4. Sanjay Monpara
    28.12.2012 12:45 | #4

    Hi vishal
    Its really helpful

    Thanks

  5. vanezy
    30.01.2013 06:19 | #5

    BOL says this option is only for scalar falued functions

  1. No trackbacks yet.