Snake Eyes Snake Eyes - 5 months ago 25
SQL Question

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

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

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