title: Raise Error in Update Statement
body: <p>I'm running a SP to update a balance in an account. Here is the SP code:</p>
  
  <pre><code>CREATE procedure UpdateBalance(@LedgerID as int,@TransDate as DateTime,@Neg as smallint) as
  Begin
  Declare @OpenBal as decimal(12,2)
  Select Top 1 @OpenBal=Balance From Trans Inner Join TransGroup On Trans.TGID=TransGroup.TGID And TransDate&lt;@TransDate Where LedgerID=@LedgerID Order By TransDate,TransGroup.TGID;
  With CTE as
  (Select TransGroup.TGID,LedgerID,Amount,Balance,Sum(Amount) Over(Order By TransDate,TransGroup.TGID) as 'Total' From Trans Inner Join TransGroup On Trans.TGID=TransGroup.TGID And TransDate&gt;=@TransDate Where LedgerID=@LedgerID)
  Update CTE Set Balance=Total+@OpenBal
  End
  </code></pre>
  
  <p>The SP runs fine and updates the balances of the account from <code>@TransDate</code> onwards.</p>
  
  <p>The <code>@Neg</code> parameter is currently unused and will have a value of either 0, -1 or 1.</p>
  
  <p>If the <code>@Neg</code> parameter is non-zero, I want the SP to fail if the sign of <code>Total+@OpenBal</code> value being updated does not match the sign of the <code>@Neg</code> parameter.</p>
  
  <p>For example, if <code>@Neg</code> is 1, then <code>Balance</code> should always be positive and the SP should fail if it becomes negative at any row.</p>
  
  <p>Of course, a simple solution is to put the Update statement in a transaction, run a Select statement to check for negative balances and then commit or rollback the transaction accordingly. But I want to know if there is a way to raise an error within the <code>Update CTE Set Balance=Total+@OpenBal</code> statement so that it automatically rolls back everything.</p>
  
  <p>I tried something like this - but learnt that <code>Throw</code> can't be called within a <code>Case</code> statement.</p>
  
  <pre><code>Update CTE Set Balance=Case @Neg When 0 Then Total+@OpenBal When 1 Then Case When Total+@OpenBal&lt;0 Then Throw Else Total+@OpenBal End When -1 Then Case When Total+@OpenBal&lt;0 Then Throw Else Total+@OpenBal End End
  </code></pre>
  
  <p>Any ideas?</p>