dbo.fn_IsEmptyField

USE [insert_database_name];
GO
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = object_id (N'dbo.fn_IsEmptyField')
AND type IN ( N'FN', N'IF', N'TF') )
BEGIN
DROP FUNCTION dbo.fn_IsEmptyField
END
GO
CREATE FUNCTION dbo.fn_IsEmptyField
(
@InputObject AS SQL_VARIANT
)
RETURNS BIT
AS
/*
*****************************************************************************
Created By: Barry Blessing
-------------------------------------------------------------------------------
Date Created: 12/01/2016
-------------------------------------------------------------------------------
Purpose: This function evaluates input for NULL-ness or empty-state,
i.e. String.Empty or NULL or ' ', etc.
Database: CMU
-------------------------------------------------------------------------------
Parameters: @@InputObject: Type SQL_VARIANT can represent any SQL or .NET type.
-------------------------------------------------------------------------------
Performance Statistics
*******************************************************************************
Date Ran: 12/01/2016

Test Statements ran:

-- THESE ARE NULL/EMPTY
SELECT dbo.fn_IsEmptyField(NULL) AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(NULL) AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(' ') AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('') AS 'RETURNED', 1 as 'SHOULD BE'

-- THESE ARE ***NOT*** NULL/EMPTY
SELECT dbo.fn_IsEmptyField('John Q Public Co.') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField('0') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('1') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(0) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(1) AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField('False') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('True') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(GETDATE()) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('12/2/2016') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(12.32) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(123456.65) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('12,3456.65') AS 'RETURNED', 0 as 'SHOULD BE'

Run time in seconds: 0
*******************************************************************************
Maintenance Log
*******************************************************************************
Date Ver PCM Author Comments
-------------------------------------------------------------------------------
12/01/2016 1.00 xxxxxx Barry Blessing Created in advance of need.
*******************************************************************************
*/
BEGIN
DECLARE @string AS VARCHAR(8000) = CAST(@InputObject AS VARCHAR(8000));
DECLARE @length AS BIGINT = LEN(LTRIM(RTRIM(@string)));
DECLARE @return AS BIT;

If @InputObject IS NULL OR @InputObject = NULL RETURN 1; -- IsNull

SET @return = CASE WHEN @length > 0 AND NOT @string IS NULL AND
NOT UPPER(LTRIM(RTRIM(@string))) = 'NULL' THEN 0
ELSE 1 -- = String.Empty or IsNull
END;

RETURN @return;
END
GO

EXEC dbo.sp_UpdateFunctionDescription @Function = 'fn_IsEmptyField',
@Description = 'Returns Boolean if String-value of input object is neither equivalent to NULL or String.Empty.'
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s