title: SQL Server - WHERE inside CASE?
body: <p>I'm fairly new to SQL Server and I'm having trouble implementing a WHERE statement inside a CASE, specifically for a trigger I'm working on.</p>
  
  <p>I have 2 tables with the following fields:</p>
  
  <pre><code>Receipt(ItemID,Quantity)
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Warehouses(WarehouseID,Stock)
  
  |WarehouseID|Stock|
  -------------------
  |ARM01      |100  |
  |ARM02      |100  |
  </code></pre>
  
  <p>The trigger checks whether a row with a specific quantity of any given item was inserted, altered or removed to Receipt and then subtracts or adds to the Stock of the Warehouses table.</p>
  
  <p>So if I add a row with 200 Quantity in Receipt, it will subtract 100 from the Stock of WarehouseID 'ARM01', and if there is no Stock left in 'ARM01' (which there isn't because there is only 100), it will subtract the other 100 from 'ARM02'. Of course if 'ARM02' has a Stock of 0 it will print an error but I'll worry about that later.</p>
  
  <p>The thing is, SQL Server doesn't accept a WHERE inside a CASE, because, of course, the entire CASE is already part of the SET statement and WHEREs only come after a SET. It's weird but I couldn't find an answer online yet for such a simple thing.</p>
  
  <p><strong>My goal:</strong></p>
  
  <pre><code>CASE 1
  UPDATE 'Stock of Table' where 'WarehouseID of Same Table' = 'ARM01'
  CASE 2
  UPDATE 'Stock of Table' where 'WarehouseID of Same Table' = 'ARM02'
  </code></pre>
  
  <p>I also realize the calculus for Stock -/+ Quantity and vice-versa might be wrong right now and I also have 2 statements in parenthesis after a THEN, separated by a AND because I was trying to distribute the first 100 to ARM01 and the rest to ARM02. That's probably very, very misguided and apologies for the incorrect code but for now I just want to figure out where to use a WHERE statement. </p>
  
  <pre><code>CREATE TRIGGER SubtractStock ON Receipt
  FOR INSERT, UPDATE, DELETE
  AS
  [snipped UPDATE for deleted because it only exchanges '+' for '-' in the operation]
  UPDATE Warehouses
  SET Stock =
      (CASE
          WHEN inserted.Quantity  &lt;= Stock THEN Stock - Quantity WHERE WarehouseID = 'ARM01'
          WHEN inserted.Quantity &gt; Stock THEN (Stock - Quantity WHERE WarehouseID = 'ARM01')
          AND (Quantity - Stock WHERE WarehouseID = 'ARM02')
          END)
      FROM inserted JOIN Warehouses ON inserted.ItemID = Warehouses.ItemID
  </code></pre>
  
  <p>Thank you!</p>