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 related to
sql
sql-server
sql-server-2008
stored-procedures
Since you're using SQL 2008:
UPDATE
table_Name
SET
column_A
= CASE
WHEN @flag = '1' THEN @new_value
ELSE 0
END + column_A,
column_B
= CASE
WHEN @flag = '0' THEN @new_value
ELSE 0
END + column_B
WHERE
ID = @ID
If you were using SQL 2012:
UPDATE
table_Name
SET
column_A = column_A + IIF(@flag = '1', @new_value, 0),
column_B = column_B + IIF(@flag = '0', @new_value, 0)
WHERE
ID = @ID
this worked great:
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
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
DECLARE @JCnt int=null
SEt @JCnt=(SELECT COUNT( ISNUll(EmpCode,0)) FROM tbl_Employees WHERE EmpCode=1 )
UPDATE #TempCode
SET janCA= CASE WHEN @JCnt>0 THEN (SELECT SUM (ISNUll(Amount,0)) FROM tbl_Salary WHERE Code=1 )ELSE 0 END
WHERE code=1
Source: Stackoverflow.com