The Thief games and Splinter Cell do a good job of getting stealth right, IMO. The most important thing is really providing the proper feedback to the player so that when they get caught, they have a clear understanding why it happened. That's why stuff like Thief's light gem and stellar audio are so important-each provided instant feedback to the player.
I'd also say that level design is also really important, and that in these games it is FAR better to make less enemies with environmental puzzles that allow the player to exploit stealth mechanics than it make huge areas with densely populated enemies, which totally suck since the game moves SO slow as you slog through a small army of baddies.
Stealth as an add-on to most games is terrible. Without familiar game rules, user feedback ,and AI behaviors, stealth can get maddeningly frustrating.
