Archive

Posts Tagged ‘return null on null input’

SQL Server – Skipping Function Execution when parameters are NULL

19.12.2012 5 comments

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