How to use Case with - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to use Case with

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-07-15 : 06:27:12

Hi,

I want to use a case statement with an if exists condition.
Please look at the code below and let me know the issue please...

Update temp_table
Set
State=@State,
StateChanged=@DateChanged ,

LastEventUid= Case When If Exists (Select * From EventHeader Where Uid=@LastEventUid) then @LastEventUid else Null

Where Uid=@AssetUid

The issue is with the Case statement.. i get following errors:
Incorrect syntax near the keyword 'If'.
Incorrect syntax near the keyword 'then'.

Thanks,
Sourav

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-15 : 06:46:25

What you can do is create a variable to hold a value and use isnull():


declare @exists int

set @exists = (Select Uid From EventHeader Where Uid=@LastEventUid)

Update temp_table
Set 
State=@State, 
StateChanged=@DateChanged , 
LastEventUid= Case When isnull(@exists,0) <> 0 then @LastEventUid else Null
Where Uid=@AssetUid

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 07:07:43

problem with this line:

LastEventUid= Case When If Exists (Select * From EventHeader
 Where Uid=@LastEventUid) then @LastEventUid else Null

should be


LastEventUid= Case When If Exists (Select * From EventHeader
Where Uid=@LastEventUid) then @LastEventUid else Null END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 13:16:10

also


Update t
Set 
t.State=@State, 
t.StateChanged=@DateChanged , 

t.LastEventUid= Case When t1.cnt>0  then @LastEventUid else Null end
from temp_table t
outer apply (Select count(*) as cnt From EventHeader Where Uid=@LastEventUid)t1
Where t.Uid=@AssetUid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources