viernes, 28 de diciembre de 2012

Skip function if NULL parameter input is passed

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

In example, this function concatenates two strings, if two arguments are passed, otherwise NULL result are returned. 

USE db
GO

CREATE FUNCTION dbo.ConcatenateSTR
(
      @string NVARCHAR(MAX),
      @string2 NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE     @retValue NVARCHAR(MAX)
SET         @retValue = @string+@string2
RETURN      @retValue
END
GO


Let's practice it:


USE db 
GO

SELECT [DB].[dbo].ConcatenateSTR(N'SQL', N'NCO')
SELECT [DB].[dbo].ConcatenateSTR(N'SQL', N'')
SELECT [DB].[dbo].ConcatenateSTR(N'SQL', NULL)
SELECT [DB].[dbo].ConcatenateSTR(NULL, N'NCO') 
GO

The final results are: 

SQLNCO 
SQL 
NULL 
NULL

Results are like expected.