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.
| Author |
Topic |
|
mattt
Posting Yak Master
194 Posts |
 Posted - 2009-01-26 : 10:47:19
|
| Just set up a stored procedure which is designed to update a table with a variety of different fields all at once. Trouble is that which fields get updated depends on what parameters are submitted to the SP.So, imagine the table ...Field1ID int,Field1Date datetime,Field2ID int,Field2Date datetime,Field3ID int,Field3Date datetimeThe sp takes parameters @field1ID, @field2ID, @field3ID.What needs to happen is that if @field1ID has a value greater than 0 then Field1ID is updated to the value of @field1ID and Field1Date is set to getdate(). This needs to happen independently of what @field2 or @field3 is .. they need their own seperate checks to do the same thing.Now obviously I could do this with a sequence of "if" clauses but in real life this SP has more than 15 parameters, and the SP is going to get extremely messy. Is there a way I can do it shorthand with a single update query? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-26 : 18:55:15
|
Do you mean something like this?update tableset Field1ID = Case When @field1ID > 0 then @field1ID else Field1ID end,set Field1Date = Case when @field1ID > 0 then getdate() else Field1Date end...... |
 |
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 2009-01-27 : 04:29:31
|
quote:
Originally posted by sodeep
Do you mean something like this?update tableset Field1ID = Case When @field1ID > 0 then @field1ID else Field1ID end,set Field1Date = Case when @field1ID > 0 then getdate() else Field1Date end......
Indeed I did. Thanks very much! |
|
|
|
|
|
|
Multi-field conditional update in one statement