Glyfair said:
The situations I'm discussing are situations where there are a very limited number of options (often just two main ones) and all of them have serious drawbacks to the character. The classic example is putting a paladin in a situation where any choices allow evil to flourish because of his actions.
This is
not a moral dilemma, although far too many folks seem to believe it is. This is one of those no-win situations you mention. Evil flourishes either way. A moral dilemma is one in which the PC (and player) are forced to figure out which choice is the moral one to make, not one in which there
are no moral choices to make.
To provide my own "classic moral dilemma" (huge, massive spoilers for the graphic novel
The Watchmen, read at your own risk):
[sblock] In the graphic novel
The Watchmen, a group of superheroes fight an archvillain. In the end they fail to prevent him from carrying out his scheme. Essentially, the archvillain kills a large, populated city in order to trick the world into backing down from World War III. When the heroes learn of this, they are faced with a terrible moral quandary, because as evil as the act was, the archvillain's plan is
working. So they have a choice. They can reveal the archvillain's plot to the world and see him pay for his crime, in which case the world starts heading back toward WWIII once more...or they can remain silent, allowing a murderer of millions to remain free, but also allowing the world to avoid the dangers of a thermonuclear holocaust.
The answer to the quandary lies in one's own sense of right and wrong, of justice, of priorities. And depending on your beliefs, there
is a right answer.[/sblock]
Now that kind of moral quandary I love, but of course they're hard to build.

(Incidentally I recommend that graphic novel to anyone.)
A recent example had a paladin placed into a situation where he could allow an evil wizard to go free, because he was the only one stopping a town from being destroyed. Either he allowed an obviously evil person to go free, or he allowed a town to be destroyed (ignore any other side issues with this one about these being the only options, because this is the way the dilemma was presented by the DM in question). The hint was that the paladin should stop being a paladin (at least requiring atonement) no matter which choice he had.
My question is, as a player, do you find that being put in such a situation adds to the game and makes it more enjoyable and fun? Even if it's not fun at the moment, does it add to your overall enjoyment of the game because it makes the world seem more "realistic"?
There is nothing realistic about providing a paladin with only two choices and vetoing whatever other solutions he may come up with. That's one of my biggest problems with many DMs' idea of a "moral dilemma." They set up scenarios like a philosophy course in college, providing only two answers and forcing--sometimes through DM-fiat with no constructed in-game reasoning at all--the player/PC to choose
only one of those two options. Philosophy is the way it is because in the real world, there are hardly
ever only two answers. So we create them theoretically to analyze our own moral values. But in real life, it's far messier. Creating the "only two options" scenario in the game is a
break from realism, not a strengthening of it.
IMO any paladin worth his salt would find a way, or try to find a way, to save the city
and neutralize the wizard. Or die trying. Paladins don't accept defeat before they've even started to fight.
To answer your straight-up question, the kind of situation you've outlined most certainly does NOT add to the game from me. Quite the opposite.