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

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

About these ads
  1. Ben
    December 23, 2012 at 4:05 pm

    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. Sanjay Monpara
    December 28, 2012 at 12:45 pm

    Hi vishal
    Its really helpful

    Thanks

  3. Alok
    December 28, 2012 at 4:21 pm

    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”.

  4. Alok
    December 28, 2012 at 4:22 pm

    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”.

  5. vanezy
    January 30, 2013 at 6:19 pm

    BOL says this option is only for scalar falued functions

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 278 other followers

%d bloggers like this: