Raise Error in Update Statement

Question

I'm running a SP to update a balance in an account. Here is the SP 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<@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>=@TransDate Where LedgerID=@LedgerID)
Update CTE Set Balance=Total+@OpenBal
End

The SP runs fine and updates the balances of the account from @TransDate onwards.

The @Neg parameter is currently unused and will have a value of either 0, -1 or 1.

If the @Neg parameter is non-zero, I want the SP to fail if the sign of Total+@OpenBal value being updated does not match the sign of the @Neg parameter.

For example, if @Neg is 1, then Balance should always be positive and the SP should fail if it becomes negative at any row.

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 Update CTE Set Balance=Total+@OpenBal statement so that it automatically rolls back everything.

I tried something like this - but learnt that Throw can't be called within a Case statement.

Update CTE Set Balance=Case @Neg When 0 Then Total+@OpenBal When 1 Then Case When Total+@OpenBal<0 Then Throw Else Total+@OpenBal End When -1 Then Case When Total+@OpenBal<0 Then Throw Else Total+@OpenBal End End

Any ideas?

Answer 1

You can't throw a custom exception within an UPDATE statement.

You have a few options:

1. Use an INSTEAD OF trigger on update. You can prevent the update from ever occurring if you want, throw a custom error, etc.
2. In the procedure, do your Total + @OpenBal computations and insert them into a table variable. SELECT for invalid values, if you find some you can throw a custom error; otherwise, join your table variable with whatever else you need and do the update

Throwing by inserting bad values is a hack and the error will be confusing to anyone using your stored procedure. It will look like your code has errors.

I am a little curious though - why would you want to store the sign separately and then verify another value has the same sign? Your design sounds denormalized.