Mirage
Mirage

Reputation: 45

Comparing String,if it is NULL in Sql Server 2008

I am using the following code to compare a string if it is equal to NULL in a Stored Procedure in Sql Server 2008.

IF(Name != '')
    // Do some stuff
ELSE IF(Phone != '')
    // Do some stuff
ELSE
    // Do some other stuff

Is there any disadvantages or drawback's in using the above format of Checking a string.

I was using the below code before:

IF(Name IS NOT NULL)
    // Do some stuff
ELSE IF(Phone IS NOT NULL)
    // Do some stuff
ELSE
    // Do some other stuff

which is not working as expected.

Upvotes: 2

Views: 3708

Answers (2)

Alex K.
Alex K.

Reputation: 175766

They are different: if Name is NULL it will never be equal to, or not equal to '' because nothing is equivalent to NULL.

The only way to test for NULL is with IS NULL.

If you want to test for NULL or '' then: IF ISNULL(val, '') = ''

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

!=/<> '' is not the same as IS NOT NULL! You need this:

IF(Name <> '')
    // Do some stuff
ELSE IF(Phone  <> '')
    // Do some stuff
ELSE
    // Do some other stuff

If Name or Phone can be NULL, you need this:

IF(ISNULL(Name, '') <> '')
    // Do some stuff
ELSE IF(ISNULL(Phone, '')  <> '')
    // Do some stuff
ELSE
    // Do some other stuff

In SQL, NULL is always <> ''. In fact, in most configurations, NULL is also <> NULL.

Upvotes: 3

Related Questions