Simple ISEMPTY() function in SQL Server throws a non-boolean type error-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

The function returns a number so you need a comparison with a number to get a boolean value

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

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

| (No column name) |
| :--------------- |
| REACHED!         |

db<>fiddle here