How to pass field name itself as a parameter?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, friends,

In our c#.net app (2.0), I have an UPDATE statement like the follows for
Oracle DB:

UPDATE contract SET phone = :phone, fax = :fax, status = :status, ....(more
fields here) WHERE contractNum = :contractNum

I know I need to pass values for each parameters before execution, something
like:

command.Parameters.Add("phone", OracleType.VarChar, 10).Value = "1234567890";

However, sometimes, some field values do not have to be changed, (e.g., the
status field value in above Update statement). At this case, can I just pass
the field name itself so that in Update statement for this field will look
like fieldname = fieldname?

For example, in this example, can I do:

command.Parameters.Add("phone", OracleType.VarChar, 10).Value = "phone";

so that the phone field value won't be changed after this Update statement
was executed. (As you see, I wished command object would interprete this as

UPDATE contract SET phone = phone, fax='0987654321',....., WHERE contractNum
= '123'

since the value is the same as field name, not

UPDATE contract SET phone = 'phone', fax='0987654321',....., WHERE
contractNum = '123')

This wish does not seem right. But that is what I try to do.

Any way to do this? Any reference papers? Thanks a lot.
 
Greetings

You could do something like

update
contract
set
phone = coalesce(@phone, phone),
fax = coalesce(@fax, fax)
where
whatever


That's TSQL syntax, though, not oracle. The coalesce will use the first
non-null value. So, if you pass null for @phone, then phone=phone, else
phone=@phone. Does oracle have an IF or CASE statement, maybe?
 
Back
Top