[SOLVED] Simple ISEMPTY() function in SQL Server throws a non-boolean type error

Issue

I’m trying to write a simple ISEMPTY function in Microsoft SQL Server:

DROP FUNCTION IF EXISTS ISEMPTY;
GO

CREATE FUNCTION ISEMPTY
    (@charsequence nvarchar(max))
RETURNS BIT 
AS
BEGIN
    DECLARE @result BIT;

    IF (@charsequence IS NULL OR LEN(@charsequence) = 0)
        SET @result = 1
    ELSE
        SET @result = 0;

    RETURN @result;
END
GO

When I want to test it with:

SELECT CASE WHEN dbo.ISEMPTY('') THEN 'REACHED!' END;

I get the following error:

[S0001][4145] Line 1: An expression of non-boolean type specified in a context where a condition is expected, near ‘THEN’.

What goes wrong here?

Solution

A boolean is expected after when in the case expression, but your function returns a bit.

Try this instead:

SELECT CASE WHEN dbo.ISEMPTY('') = 1 THEN 'REACHED!' END;

Answered By – Zakaria

Answer Checked By – Candace Johnson (BugsFixing Volunteer)