title: SQL Server procedure returning dbNull instead of sum
body: <p>I have the following stored procedure in SQL Server 2005:</p>
  
  <pre><code>ALTER PROCEDURE dbo.GetBondAmounts
      (
      @Username varchar(20)
      )
  AS
      SELECT Bond.ID BondID, Powers.BondAmount + Charges.BondAmount BondAmount,
      BondFee.Amount + Powers.BondPremium + Charges.BondPremium + Forfeiture.CostOfApprehension + Forfeiture.AmountPaid BondTotal,
      BondFee.Amount + Powers.BondPremium + Charges.BondPremium + Forfeiture.CostOfApprehension + Forfeiture.AmountPaid
      - BalanceForgiveness.Amount - Payment.Amount BondBalance
      FROM Bond LEFT OUTER JOIN UserAgency ON Bond.Agency = UserAgency.Agency
      LEFT OUTER JOIN
      (
          SELECT BondID, SUM(AmountForgiven) Amount
          FROM BalanceForgiveness
          GROUP BY BondID
      ) AS BalanceForgiveness ON Bond.ID = BalanceForgiveness.BondID
      LEFT OUTER JOIN
      (
          SELECT Bond, SUM(Amount) Amount
          FROM BondFee
          GROUP BY Bond
      ) AS BondFee ON Bond.ID = BondFee.Bond
      LEFT OUTER JOIN
      (
          SELECT Powers.Bond, SUM(Charge.BondAmount) BondAmount,
          SUM(Charge.BondPremium) BondPremium
          FROM Powers INNER JOIN Charge ON Powers.Surety = Charge.PowerSurety
          AND Powers.PowerPrefix = Charge.PowerPrefix AND Powers.PowerNumber = Charge.PowerNumber
          GROUP BY Bond
      ) AS Powers ON Bond.ID = Powers.Bond
      LEFT OUTER JOIN
      (
          SELECT BondID, SUM(BondAmount) BondAmount, SUM(BondPremium) BondPremium
          FROM ChargeWithoutPower
          GROUP BY BondID
      ) AS Charges ON Bond.ID = Charges.BondID
      LEFT OUTER JOIN
      (
          SELECT Bond, SUM(CostOfApprehension) CostOfApprehension, SUM(AmountPaid) AmountPaid
          FROM Forfeiture
          GROUP BY Bond
      ) AS Forfeiture ON Bond.ID = Forfeiture.Bond
      LEFT OUTER JOIN
      (
          SELECT Bond, SUM(Amount) Amount
          FROM Payment
          GROUP BY Bond
      ) AS Payment ON Bond.ID = Payment.Bond
      WHERE UserAgency.Username = @Username
      OR EXISTS (SELECT * FROM Users WHERE Username = @Username AND Admin = 1)
  </code></pre>
  
  <p>I am getting a dbNull value for BondAmount, BondTotal, and BondBalance.  Sometimes there will be no Charge rows or no BalanceForgiveness rows, etc.  I think the issue I am getting is that when there is one table that contains no data for the row, that entire calculation becomes null... how do I fix this to give me the correct amount, total, and balance for each and every bond, no matter how many tables have data or don't.</p>