If you mean just having sex incorporated into the story of a game, rather than it being the game itself, I think it simply wouldn't work very well. Have you seen even a convincing in-game kiss? It's always laughable. Mass Effect is the closest I can think of recently of a game including any kind of physical relationship with your game character, and although it was fun to annoy Daily Mail readers, I don't know if it really added anything to the gameplay. :?
I thought Mass Effect handled it really well. Despite the polygonal characters I thought it felt emotional, it looks fine... it made sense for the story.
I know in Alpha Protocol I want to bang some hot spy babes. That is just part of the spy movie culture... if it just cuts to a black screen that is kind of lame, I want some kind of setup or something. Immature? Maybe, but it depends on subject matter... the brief glimpses of sex in the Dragon Age trailers looks kind of silly, honestly, but a modern day spy RPG? I want to see sex and boobs and I want some of the girls to talk like porn stars, I don't even care.
Depends on the game.