Man, on the second page and there's been no discussion of how the core Metal Gear Solid games handle this (hint: awesomely). Edit: I have since been ninja'd by shrekfan.
MGS thrives on the manipulation of win states for each section of the game. There's no incentive for killing enemies apart from bosses, and direct combat is costly in terms of your limited resources. Ammo, health, healing items, that kind of thing. Most enemies in most of the games don't even drop ammo most of the time, so any kind of combat engagement represents a loss of resources. Unless you take the risk of sneaking up behind them and dispatching them with a hand-to-hand move, which in itself may cost you HP as a resource if you mess up.
Combat is generally balanced in such a way that direct combat against whatever opposition you're facing is generally not an option for game progression. It is, however, an option for making a tactical retreat; you can fight enemies to create an opening through which to escape. So the balance of the game makes combat engagements workable when necessary, but otherwise best avoided. It also helps that many sections have respawning enemies, meaning that even if you do manage to slaughter everyone, that victory is only temporarily meaningful. It'll buy you a moment before reinforcements arrive, then you're back where you were. With less resources.
The camo system introduced in MGS3 also added a really interesting element. Rather than relying on cover and moving from A to B while enemies were otherwise occupied or unaware, you could hide in plain sight as long as your movements didn't draw attention. This gave rise to some really tense, "cinematic" moments where an enemy may almost walk directly into you, but not notice you. But make one wrong move...
Mind you, the MGS series has plenty of flaws, too. But they handle stealth in a really interesting way. Honourable mention goes to the Rebellion Aliens vs. Predator games, where Aliens can break environmental light sources and hide from human AI (and other human players!) in the resultant darkness. Again, this breaks the standard "A to B" thing where A and B are hiding spots. It's a bit more emergent since, like with the MGS camo system, you essentially decide what a hiding spot is or isn't. Combined with the unique mobility mechanics for the Aliens, this creates room for some very interesting and creative player strategies. And I may as well mention the original Assassin's Creed, as repetitive as it was. In the original game, you weren't some kind of invincible combat god as you are later on, so you actually had to rely on stealth and mobility to get your job done and make a quick exit. Mechanics that allowed you to blend into the population of a city really helped aid this style of gameplay, since you needed a game-approved hiding spot, but some of those hiding spots could move.