>>>Hi,
>>>
>>>Simplified case:
>>>In a stored procedure I declare a variable then the variable either gets a value or not (the value is actually retrieved from pass XML file).
>>>Then I need to Insert a record and set the value to this column if the value exists, or do not include this column in the Insert (if the value is NULL).
>>>Example that generates error:
>>>

>>>DECLARE @Field1Value AS CHAR(10)
>>>INSERT INTO MyTable( Field1) values (COALESCE(@Field1Value, Field1))
>>>

>>>
>>>So far, the only solution I see is to Insert the record field, get the PK (right after insert using
>>>select @PkValue = SCOPE_IDENTITY() ). And then UPDATE the record where COALESCE() can be used.
>>>
>>>Can anybody see/suggest an approach of doing it all in one Insert (instead of Insert and Update)?
>>>
>>>TIA
>>
>>During the INSERT the Field1 is NULL because you insert new record.
>>You can insert empty value:
>>

>>INSERT INTO MyTable( Field1) values (COALESCE(@Field1Value, ''))
>>

>
>This works for char type fields. What if the field is DateTime type? (I suppose for Decimal or Int, I can default to 0).

For datetime you don't need COALESCE, just insert NULLs there :-)
(or if you have some other value as default value of DT column).