Problem handling NULL values in SQL Server 2000

Hi I have a function with if and else conditions. For some reason the
second if condition does not seem to work. It is not able to check for
Null values.
I am new to SQL programming and so I cant figure out the actual
problem.
Any help would be greatly appreciated. Cheers!
Here is the function.
ALTER FUNCTION dbo.labelSalutation ( @.moSurname varchar(50),
@.faSurname varchar(50) )
RETURNS varchar(50)
AS
BEGIN
DECLARE @.res varchar(50)
IF (@.moSurname = @.faSurname or @.moSurname = null)
BEGIN
SET @.res = 'The '+@.faSurname+' Family'
END
ELSE
IF (@.faSurname = NULL and moSurname <> Null) /* <-- This
condition does not work */
BEGIN
SET @.res = 'The '+@.moSurname+' Family'
END
ELSE
IF (@.moSurname<> @.faSurname)
BEGIN
SET @.res = 'The '+@.moSurname+' & '+@.faSurname+' Family'
END
RETURN @.res
ENDOn Aug 29, 9:57 am, Rex <rakesh...@.gmail.com> wrote:
> Hi I have a function with if and else conditions. For some reason the
> second if condition does not seem to work. It is not able to check for
> Null values.
> I am new to SQL programming and so I cant figure out the actual
> problem.
> Any help would be greatly appreciated. Cheers!
> Here is the function.
> ALTER FUNCTION dbo.labelSalutation ( @.moSurname varchar(50),
> @.faSurname varchar(50) )
> RETURNS varchar(50)
> AS
> BEGIN
> DECLARE @.res varchar(50)
> IF (@.moSurname = @.faSurname or @.moSurname = null)
> BEGIN
> SET @.res = 'The '+@.faSurname+' Family'
> END
> ELSE
> IF (@.faSurname = NULL and moSurname <> Null) /* <-- This
> condition does not work */
> BEGIN
> SET @.res = 'The '+@.moSurname+' Family'
> END
> ELSE
> IF (@.moSurname<> @.faSurname)
> BEGIN
> SET @.res = 'The '+@.moSurname+' & '+@.faSurname+' Family'
> END
> RETURN @.res
> END
Replace IF (@.faSurname = NULL and moSurname <> Null) with IF
(@.faSurname is NULL and moSurname is not Null)