Bill not "Dan". :-)
Pretty sure IIF() was added in SQL Server 2012. if you (will) have no need to support earlier versions of SQL Server and/or you will have no requirement to port your code to a different SQL engine then for simple cases, IIF() is (IMO) a simpler syntax than CASE. YMMV and you have to consider your use own cases. But who am I to argue with gurus? :-)
Bill
>Hi Dan,
>
>But I thought that IIF() only works with VFP; and for SQL Server stored procedure I need a CASE.
>Did I miss something?
>Thanks,
>
>>Hey Dmitry,
>>
>>Instead of CASE statement you might try the IIF() syntax so the code is a little simpler.
>>
>>>Hi,
>>>
>>>I have a long stored procedure which updates a record in a table. There is a long UPDATE command in the SP, as follows (simplified):
>>>
>>>UPDATE mytable set field1 = COALESCE(@field1Val,field1), field2 = COALESCE(@field2Val,field2),
>>> field3 = COALESCE(@field3Val,field3) where PK_VALUE = @PkValue
>>>
>>>
>>>The above works but overrides the value in the field. For example, say I want to append the field "field2" with the value in the variable @field2Val, instead of overriding it.
>>>
>>>How do I change the above segment of the SP?
>>>
>>>TIA
>>>
>>>UPDATE. I figured that I can use the CASE THEN ELSE END in the UPDATE expression. All set.
William A. Caton III
Software Engineer
MAXIMUS
Atlanta, Ga.