Using "IS" in an IIF function - 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

Using "IS" in an IIF function

Author

Topic

waveform
Yak Posting Veteran

93 Posts

Posted - 2011-11-30 : 22:54:41

I have an UPDATE statement which is causing an error in the following assignment:

JobClosedUTC = IIF(JobClosedUTC IS NULL, GETUTCDATE(), JobClosedUTC)

I'm wanting to assign the current time to JobClosedUTC only if it's NULL, otherwise leave it alone. But this code is throwing an error "Incorrect syntax near the word 'IS'".

I thought you could use logical tests in the first parameter of an IIF function? Is there another way to achieve this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-30 : 23:56:15

are you using SQL Server ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 00:04:54

IIF is not available until Denali
are you using Access?

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

waveform
Yak Posting Veteran

93 Posts

Posted - 2011-12-01 : 01:17:28

Apologies, yes I'm using SQL Server 2008.

Strange, because IIF is documented even in the SQL Server 2000 HTMLHelp file.

http://i.imgur.com/oxVsH.gif

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 01:33:58

quote:
Originally posted by waveform

Apologies, yes I'm using SQL Server 2008.

Strange, because IIF is documented even in the SQL Server 2000 HTMLHelp file.

http://i.imgur.com/oxVsH.gif

if you're using sql 2008 you should be using CASE...WHEN instead

IIF is not documented in SQL 2000 help file. You might have seen it in VB help file

anyways using CASE WHEN it will be like below

JobClosedUTC = CASE WHEN JobClosedUTC IS NULL THEN GETUTCDATE() ELSE JobClosedUTC END

or as a shortcut

JobClosedUTC =COALESCE(JobClosedUTC,GETUTCDATE())

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources