Comparing 2 variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing 2 variables

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2010-08-25 : 00:27:00

How do you compare 2 variables in an IF statement when one variable can potentially be null or empty.

I have the following stored proc:

ALTER PROCEDURE [dbo].[UPD_VACATION]
@pID int,
@pVacationDays decimal(6,2)
AS

DECLARE @currentVacationDays as decimal(6,2)
SET @currentVacationDays = (SELECT TotalVacationDays FROM VacationTable WHERE ID = (SELECT MAX(ID) FROM VacationTable WHERE EmployeeID = @pID))

IF (@pVacationDays != @currentVacationDays)
BEGIN
INSERT INTO VacationTable
("EmployeeID", "TotalVacationDays")
VALUES
(@pID, @pVacationDays);
END
END

If there is a value returned the SET @currentVacationDays = (SELECT TotalVacationDays FROM VacationTable WHERE ID = (SELECT MAX(ID) FROM VacationTable WHERE EmployeeID = @pID)) statement it works as intended.
However, if there is no value returned in that statement the
IF(@pVacationDays != @currentVacationDays) compare is false.

When I step through the stored proc and look at the values it looks like this:

IF(5.00 != {...})

Shouldn't this be true? Or can you not compare a value to "empty"

Thanks in advance.

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-25 : 02:46:15

Hello,

Try this:

...
IF @currentVacationDays IS NULL OR (@currentVacationDays IS NOT NULL AND @pVacationDays<>@currentVacationDays)
BEGIN
....
END

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-25 : 02:56:17

Its false since NULL is an unknown value. You can handle NULL using ISNULL,case or COALESCE depending on your requirements.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:30:33

quote:
Originally posted by Devart

Hello,

Try this:

...
IF @currentVacationDays IS NULL OR (@currentVacationDays IS NOT NULL AND @pVacationDays<>@currentVacationDays)
BEGIN
....
END

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

You dont need the additional check. thats redundant

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources