SQL:UserDefinedFunction:IsEmptyString

出自ProgWiki

用途

• 仿 ISNULL( check_expression , replacement_value ) , 但對於對於空字串的作法有分別

程式碼

-- 物件:  UserDefinedFunction [dbo].[IsEmptyString]
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[IsEmptyString](
@strCheck AS NVARCHAR(1024),
@strReplacement AS NVARCHAR(1024)
) RETURNS NVARCHAR(1024)
AS
BEGIN
DECLARE @strRet AS NVARCHAR(1024);
IF(@strCheck<>'') SET @strRet=@strCheck;
ELSE SET @strRet=@strReplacement;
 
RETURN @strRet;
END

傳回值比較

• 測試用

SELECT [dbo].[IsEmptyString](null, 'X'), ISNULL(null, 'X')
SELECT [dbo].[IsEmptyString]('', 'X'), ISNULL('', 'X')
SELECT [dbo].[IsEmptyString]('A', 'X'), ISNULL('A', 'X')
SELECT [dbo].[IsEmptyString](null, null), ISNULL(null, null)
SELECT [dbo].[IsEmptyString]('', null), ISNULL('', null)
SELECT [dbo].[IsEmptyString]('A', null), ISNULL('A', null)

• 傳回值

'X','X'
'X',''
'A','A'
NULL,NULL
NULL,''
'A','A'