I have a SQL server table in which there are 2 columns that I want to update either of their values according to a flag sent to the stored procedure along with the new value, something like:
UPDATE table_Name SET CASE WHEN @flag = '1' THEN column_A += @new_value WHEN @flag = '0' THEN column_B += @new_value END AS Total WHERE ID = @ID
What is the correct SQL server code to do so??
This question is tagged with
~ Asked on 2013-09-08 10:06:20
The current answers are fine and should work ok, but what's wrong with the more simple, more obvious, and more maintainable:
IF @flag = 1 UPDATE table_name SET column_A = column_A + @new_value WHERE ID = @ID; ELSE UPDATE table_name SET column_B = column_B + @new_value WHERE ID = @ID;
This is much easier to read albeit this is a very simple query.
Here's a working example courtesy of @snyder: SqlFiddle.
~ Answered on 2013-09-08 10:15:23
Something like this should work:
UPDATE table_Name SET column_A = CASE WHEN @flag = '1' THEN column_A + @new_value ELSE column_A END, column_B = CASE WHEN @flag = '0' THEN column_B + @new_value ELSE column_B END WHERE ID = @ID
~ Answered on 2013-09-08 10:09:39