NULL DOESN'T BEHAVE IN STORED PROCEDURES

[Rob1412]

I have this function but the if does'nt seem to be working correctly , I believe it's because of the comparison with null, I've had this problem before but gotten around it using the 2nd method which i'm not really fond of.

---------------method wanted
ALTER FUNCTION RETURN_BLOQUEO
(@prmMsisdsn VARCHAR(13),@prmServicio VARCHAR(13))
RETURNS int
AS
BEGIN


IF (SELECT MSISDN FROM dbo.View_Blacklist_Aplication
WHERE MSISDN=@prmMsisdsn AND SERVICIO=@prmServicio) = NULL

RETURN 0

RETURN 1
END



---------------------second method
ALTER FUNCTION RETORNAR_BLOQUEO (@prmMsisdsn VARCHAR(13),@prmServicio VARCHAR(13))
RETURNS int
AS

BEGIN

DECLARE @MSISDN VARCHAR(13)

set @MSISDN=(SELECT MSISDN FROM dbo.View_Blacklist_Aplication
WHERE MSISDN=@prmMsisdsn AND SERVICIO=@prmServicio)

IF ISNULL(@MSISDN,'NULL') = 'NULL'
RETURN 0


RETURN 1
END

 

[cheyney]

use IS NULL instead of = NULL


---------------method wanted
ALTER FUNCTION RETURN_BLOQUEO
(@prmMsisdsn VARCHAR(13),@prmServicio VARCHAR(13))
RETURNS int
AS
BEGIN


IF (SELECT MSISDN FROM dbo.View_Blacklist_Aplication
WHERE MSISDN=@prmMsisdsn AND SERVICIO=@prmServicio) IS NULL

RETURN 0

RETURN 1
END



---------------------second method
ALTER FUNCTION RETORNAR_BLOQUEO (@prmMsisdsn VARCHAR(13),@prmServicio VARCHAR(13))
RETURNS int
AS

BEGIN

DECLARE @MSISDN VARCHAR(13)

set @MSISDN=(SELECT MSISDN FROM dbo.View_Blacklist_Aplication
WHERE MSISDN=@prmMsisdsn AND SERVICIO=@prmServicio)

IF ISNULL(@MSISDN,'NULL') = 'NULL'
RETURN 0


RETURN 1
END


cheyney

 

[cheyney]

a simple search in Books Online for "null" would lead you to "null values", and underneath that there is an entry entitled "comparison search conditions"...alot of problems posted to this forum can be easily resolved by spending 5 minutes with the documentation

Cheyney

 

[SQLBill]

Remember this:

= NULL looks for a word (string)

ISNULL looks for the NULL value

-SQLBill

 

[pankajv]

As far as my knowledge goes it all depends on the setting
of ANSI NULL. using IS NULL for null values will always work but = NULL will work only based on the ANSI NULL settings.

Correct me if I am wrong.

Thanks,
Pankaj

 

[JamesLean]

Pankaj, you are correct. = NULL will only work if ANSI_NULLS are OFF.

In any case, a much better method of checking in this scenario is to use EXISTS:

IF NOT EXISTS (
  SELECT MSISDN
  FROM dbo.View_Blacklist_Aplication
  WHERE MSISDN = @prmMsisdsn
    AND SERVICIO = @prmServicio
)
  RETURN 0
ELSE
  RETURN 1

--James