While none of the endings of Silent Hill 2 are truly 'bad' in a quality sense, even the joke endings are at least amusing, I honestly think that "Leave" is the most narratively fulfilling one. Ultimately, my reasoning's pretty simple. Late into the game, we see the deaths (explicit and implicit) of Eddie and Angela, and even before then we see their respective descents until it's clear that their fates were unavoidable. Eddie refused to accept responsibility for himself or his actions, and tried to take them out on someone who was willing and able to fight back; while Angela couldn't get over the guilt of what she'd done, no matter how justified she may have been, and that consumed her as much as the fire did.
Disregarding the NG+ endings for now, "Maria" sees James end up the same way as Eddie, claiming that he'd done and was doing nothing wrong by rejecting Mary in favor of Maria. "In Water", on the other hand, sees James unable to move on, drowning both literally in the lake and figuratively in the guilt of killing Mary. The thing is, after seeing what happened to the other two, and the flaws that they failed to overcome, it makes more sense in my opinion that he would be able to overcome them himself, especially after his reaction to the twin Pyramid Heads that he "[doesn't] need you anymore". That statement could be taken in a lot of ways, but it makes the most sense to me that rather than "I've done nothing wrong" or "I can punish myself", the meaning of this statement should be "I know what I've done and I've accepted it."
To cover the NG+ endings briefly, since they're less influential on my thoughts on the game than the main ones: "Rebirth" leans a bit too far into the original game's cult setting that SH2 was mostly trying to avoid, "UFO" and "Dog" are outright jokes rather than complete endings, and in the remake, "Bliss" leaves the story unfinished for the sake of 'a happy ending' (and yes, I know that's completely intentional) while "Stillness", admittedly the best of that group, feels more like an extension of "In Water", and as good of one as it is, I still believe that "Leave", in and of itself, is still the 'best' ending.
Rather than falling into the line of one of his fellow sufferers of Silent Hill, or getting lost in an entirely different direction, James finding his own path and his salvation feels like the most appropriate way that Silent Hill 2 could end. Sure, it's grim, dark and depressing, and so's the human experience that the game is about, but some amount of light is important to that experience too, and Mary's letter is the greatest proof of that that the game has to offer.
"James... You made me happy."
