user1221989 user1221989 -3 years ago 87
SQL Question

Updating table data in SQL Server

I have the below SQL statement. What I am trying to do is add two values based on conditions. So if

@IsUCMPresent
is 1 then I should get 75*70 but if
@IsEVMPresent
is also present I should be getting (75 * 70) + (75 * 8)

SET @IsEVMPresent = 1
SET @IsUCMPresent = 1

UPDATE #MainData
SET OneTimeExpense = (CASE WHEN @IsUCMPresent = 1 THEN 75 * 70 END) +
(CASE WHEN @IsEVMPresent = 1 THEN 75 * 8 END)
WHERE ItemId = 'ECS' OR ItemId = 'UCM'


The issue is that if
@IsUCMPresent = 1
is true then I get the sum just fine but if only
@IsEVMPresent = 1
is true then I don't get 75 * 8

Answer Source

You need to add an else clause to your case statements:

UPDATE #MainData
    SET   
    OneTimeExpense  = (Case When @IsUCMPresent = 1  Then 75 * 70 Else 0 End) +
                      (Case When @IsEVMPresent = 1  Then 75 * 8  Else 0 End)
Where ItemId IN('ECS','UCM')

Without them it will return null and Sql cannot add a number and null.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download