When @TxtGrant is NULL, CharIndex should return a NULL value.  The comparison > 0 should return false.  If you want the comparison to return true when @TxtGrant is NULL...

I think this will perform better.

SELECT Distinct F.EventID, CauseID

   FROM Faults F INNER JOIN (SELECT MAX(Probability) MaxProb, EventID

      FROM Faults GROUP BY EventID) MP

      ON (MP.MaxProb=F.Probability and MP.EventID=F.EventID)

   WHERE CauseID='Banana Peel'

-Karl

This is clearly a semi-manual process.  Consequently, I would not try to solve it in one-fell-swoop.  

1. Use both VB and SQL to do portions of the job. 
2. Work right to left.  
3. Using VB...