So you're looking for games that AREN'T RPGs and offer a great story. And you're also asking how they're incorporated.
FPS genre:
Half-Life / Half-Life 2 - HL basically invented the immersion FPS. I kinda just made that term up, lemme describe. Instead of the game cutting away to an FMV, you're completely within gameplay as the story unfolds in front of you. In that sense, it makes the whole game a lot more immersive as you look around while a character is talking to you. Here's a great example, near the beginning of HL2 when the combine realize you're in the city, Barney walks out and gives you your crowbar and comments that he's never seen the Citadel lit up so much before. Directly ahead of you in the distance you can see the citadel's massive panels opening and releasing thousands of camera drones into the air to search for you, and you can even use the suit's zoom function to get an even better look. If you're not really looking for story as much, details like this would be lost on ya.
F.E.A.R. - In addition to also being an immersive styled FPS, the theme is very isolated and little character interaction takes place to develop the depth of the story. How is this remedied? In rooms you'll find corporate phones with messages from employees, either begging for help or describing hearing something happened, etc. You'll also hear radios that report broadcasts about the places you've just been and what the public believes it to be the work of.
Stealth genre:
Metal Gear Solid - Yes, I'll repeat the other posts, MGS has an amazing story, and a lot of it takes place over the codec. If you're just looking for action you can usually skip a lot of this, but if you're looking for story, don't.
Horror genre:
Silent Hill - Well, not only is the story linked heavily into the places you end up in (for example, Harry finds a phone and hears his daughter crying for his help somewhere else in the midst of a 'dungeon'), there are also multiple endings. 5, to be exact.
System Shock/2 - If you haven't heard of this series, I'd recommend looking into it.
Platformer genre:
Megaman Zero - Very heavy on story between missions, something the other Rockman games don't seem to accomplish as well.
RTS genre:
Command and Conquer - C&C was basically a pioneer in FMVs, using them to brief you to missions you're about to accomplish. It does so in an immersive fashion as well. Instead of cutting from your point of view, characters refer to you as commander in the first person and you're not directly cut away from the gameplay. The Tiberium series also has an amazing storyline full of depth [and I will admit I'm an avid C&C fan since playing the original and red alert].
I guess thats all I'll mention for now, good stories exist in plenty of different games, they're simply left as fragments in different ways. It's always up to the player to pick up the pieces and put them together, which simply makes things more fun and gives it more replay value. Oh yeah, forgot to mention one other great one, but dunno which genre it's classified under: Zone of the Enders.
