SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-------------------------------------------------------------------------------
Author: Francesco Bosetti
Create date: 2015-04-08
Description: come la funzione NZ() di MS Access:
se il @value è NULL o '', ritorna @valueIfNull
-------------------------------------------------------------------------------
*/
CREATE FUNCTION NZ
(
@value AS nvarchar(MAX)
, @valueIfNull AS nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @res AS nvarchar(MAX)
SET @res = CASE WHEN ISNULL(@value, '') = '' THEN @valueIfNull ELSE @value END
RETURN @res
END
GO
Some tests:
DECLARE @A AS NVARCHAR(100) = 'AAAA' DECLARE @B AS NVARCHAR(100) = '' DECLARE @C AS NVARCHAR(100) = NULL SELECT @A, @B, @C SELECT dbo.NZ(@A, '1'), dbo.NZ(@B, '2'), dbo.NZ(@C, '3')
Results:
a b c ---------- ---------- ---------- AAAA NULL a b c ---------- ---------- ---------- AAAA 2 3
Another version here.
