Is it pobbile to us a variable in a MS SQL query like this:
DECLARE @ChrgTot MONEY
(SELECT @ChrgTot = ISNULL(SELECT SUM Amt FROM Charges WHERE tre1.TreatId = Charges.TretId), 0) As Total,
50000 - @ChrgTot As Balance
FROM Treatments tre1
The reason for this request is that the subquery is quite lengthy and I don't want to have to repeate it several times within the query. If I could execute the sub query once, then use it throughout the rest of the query, that would be great.
Any ideas would be much appreciated.