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