IS NULL or IS NOT NULL from CASE Expression

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

IS NULL or IS NOT NULL from CASE Expression

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2012-04-03 : 19:24:54

Hi,

I am getting the error "Incorrect Syntax near the keyword IS" from the following example:

declare @Employee table (
Name varchar(10),
TerminationID int
)

insert into @Employee (
Name,
TerminationID
)
values
('Bob', null),
('Doug', null),
('Bill', null),
('Steve', 2)

DECLARE @ViewTerminatedEmployees bit = 1

SELECT * FROM @Employee E
Where
CASE
WHEN @ViewTerminatedEmployees = 1 THEN E.TerminationID IS NOT NULL
ELSE E.TerminationID IS NULL
END

Why can't I return IS NULL or IS NOT NULL from the CASE Expression?

Can someone help with an alternative solution?

Thanks!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-03 : 19:32:46

[code]
WHERE (@ViewTerminatedEmployees = 1 AND E.TerminationID IS NOT NULL )
OR (@ViewTerminatedEmployees <> 1 AND E.TerminationID IS NULL )
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rypi
Yak Posting Veteran

55 Posts

Posted - 2012-04-03 : 23:38:15

Thanks khtan!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources