IsNullOrEmpty – A user Defined Function in SQL server - SQL Programmers

IsNullOrEmpty – A user Defined Function in SQL server

12/12/2011

The IsNullOrEmpty() is a user defined function which is used to check the given variable is null or empty.

Creating a function

Create the user defined function IsNullOrEmpty() in SQL Server

CREATE FUNCTION [dbo].[IsNullOrEmpty](@MyValue VARCHAR(MAX))
RETURNS BIT
AS
BEGIN
    IF ISNULL(@MyValue, '') = ''
    BEGIN
        RETURN 1
    END
   
    RETURN 0
END

The above IsNullOrEmpty() function will accepts the string value. If the value is empty then return the bit value 1 other wise will return the value 0

Call the IsNullOrEmpty() function

The following code will return 0 and 1. because first select code will return 0 because MyValue variable contains the value ‘Sri’ and secton select statement returns 1, because the MyValue variable assigned the value ‘NULL’

DECLARE @MyValue VARCHAR(50)
SELECT @MyValue = 'Sri'
SELECT dbo.IsNullOrEmpty(@MyValue)
SELECT @MyValue = NULL;
SELECT dbo.IsNullOrEmpty(@MyValue)

Output

0
1