String.IsNullOrEmpty like function for VARCHARs in SQL?

Here is my function that "extends" ISNULL and checks for empty as well. The test value is checked for null and if it is not null it is trimmed and then checked for length.

The function returns the test string if it is NOT Null or Empty, otherwise the second string is returned.

CREATE FUNCTION [dbo].[ISNULLOREMPTY]
(   
    @value NVARCHAR(max),
    @return NVARCHAR(max)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN 

IF (@value IS NULL)
BEGIN
    RETURN @return
END
ELSE
BEGIN
    IF (LEN(LTRIM(@value)) = 0)
    BEGIN 
        RETURN @return
    END 
END

RETURN @value;
END
GO

If I'm concatenating or coalescing a string inline (within a select statement), and I want to check if the column is NULL or Empty, I do this:

ISNULL('starting to build string ' 
+ NULLIF(some_table..some_col_that_might_be_null_or_empty, '')
, 'string to append if the resulting concatenation is null')

The NULLIF on the inner part of the expression will force the column to be NULL if it's empty, then the outer ISNULL expression can depend on consistent input and react accordingly.

IF COALESCE(@SomeVarcharParm, '') <> ''
BEGIN
   -- do stuff
END

You can do ISNULL(@SomeVarcharParam, '') <> '' or you can create a UDF that returns a bit:

create function dbo.IsNullOrEmpty(@x varchar(max)) returns bit as
BEGIN
IF @SomeVarcharParm IS NOT NULL AND LEN(@SomeVarcharParm) > 0
    RETURN 0
ELSE
    RETURN 1
END

And call that using IF NOT dbo.IsNullOrEmpty(@SomeVarcharParam) BEGIN ...

Keep in mind that when calling a UDF, you MUST prefix the owner of it (here, dbo.)