How do check if a parameter is empty or null in Sql Server stored procedure in IF statement?

Question

I read this: How do I check if a Sql server string is null or empty but it not helped me in this situation.

The piece of code from my stored procedure:

IF (@item1 IS NOT NULL) OR (LEN(@item1) > 0)
        SELECT @sql = 'SELECT * FROM TEST1'
    ELSE
        SELECT @sql = 'SELECT * FROM TEST2'
 PRINT @sql;

@item1 is NVARCHAR(1000) type.

When execute this stored procedure, I provided the value for item1

EXEC    [dbo].[my_proc]
        @item1 = N''

it shows

SELECT * FROM TEST1 // it is correct if @item1 = N'some'

instead of

SELECT * FROM TEST2

It is somewhere a function in sql to verify if a string is null or empty OR I made somewhere a mistake ?

Like in C# -> string.IsNullOrEmpty(myValue)

Answer 1

that is the right behavior.

if you set @item1 to a value the below expression will be true

IF (@item1 IS NOT NULL) OR (LEN(@item1) > 0)

Anyway in SQL Server there is not a such function but you can create your own:

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

Answer 2

To check if variable is null or empty use this:

IF LEN(ISNULL(@var, '')) = 0
    -- Is empty or NULL
ELSE
    -- Is not empty and is not NULL

Answer 3

Of course that works; when @item1 = N'', it IS NOT NULL.

You can define @item1 as NULL by default at the top of your stored procedure, and then not pass in a parameter.

Answer 4

Firstly, check if the value is empty then transform as null
and then check if null , then make as zero
Finally check if equal to zero

IF ISNULL(NULLIF(@item1,''), 0) = 0
BEGIN
     SELECT @sql = 'SELECT * FROM TEST2'
END
ELSE
BEGIN
    SELECT @sql = 'SELECT * FROM TEST1'
END

Answer 5

I used the following in 2012 and it worked:

if (@MyCode  != '' and @MyCode is not null)
BEGIN
 --statements if not null or empty
END
ELSE
BEGIN 
 --statements if null or empty
END