Legend of Zelda: Ocarina of Time. That game had a great mix of combat and puzzle to keep the action and intensity up, while providing great puzzles to keep your mind on its toes along with your fingers; and all that led up to memorable boss fights. The story spanned ages, littered with a ton of interesting and fun characters. Along with the main gameplay and story, it added side gameplay and side stories with all the side quests you could do, which were also incredibly fun.
Also, I personally think Gameplay is the most important aspect of a game. The gameplay has to make me want to play the game. If the gameplay is terrible, I'm not going to care how great the story is or how fantastic the graphics are, I'm not going to play it.
Anything by Bioware is great, and as much as I'd like to say they are the best, I did see MGS3: Snake Eater in there, and that has to be the best example of excellent game play and extraordinary storytelling that I've ever experienced. Here's a small list of a few favs. (I'd like to say Chrono Trigger, but I'd just be playing favorites, the story is an 90's video game story. It's good, but it's not very mature. Mature as in grown up, not adult)
Star Wars: Knights of the Old Republic(1) MGS3: Snake Eater Mass Effect (1) Prince of Persia (with the claw)
Im struggling to come up with a concrete example at the moment but if a game has shitty mechanics it takes you out of the story so no matter how well something written or planned if you cant play the game in a fluid and intuitive manner the suspension of disbelief is lost and youre fighting the games flaws instead of being immersed by its imagery and scenery.
A fun game has good gameplay, a good game has good game play and a little story to drive it along, the most critically acclaimed games in existence have brilliant game play and engaging stories and scenarios.
Yes it doesn't take a story to make a fun game that people will want to buy (like Limbo for example), but throw in a good story as well and you've got a gem!
Almost every game in my library is story heavy or at least has a good scenario and premise that draws you in. Sadly, good story writing is hard to come by these days so you can imagine how small my library is to begin with :b
Want a story oriented game that will keep you glued to your seat? Try the Ratchet and Clank PS3 trilogy, pretty proof in the pudding that a good story can make a game infinitely more enjoyable than just wizz-bang-shooty-fun on its own.
I voted no and my examples would be pretty much any Zelda game. Primarily Ocarina of Time, Majora's Mask and Twilight Princess. A good game will pull you into it's world and you won't even mind the occasional (and usually necessary) in-game tutorials on how to use the controls.
Oh, I said "yes," you don't need story to have good game play. As a matter of fact, I think games that have an awesome story but the worse game play ever are some of the worst games ever. I think a game should be judged on the game play apart from story. Once the game play is awesome, then start with the story aspect. I think a developer team should never compromise on the actual game play for an otherwise masterful storyline. It should have both, or by all means at least the game play. If the game can keep gamers happy for hours without a story, then you'll know you've succeeded in making a great game - and a great base for which to build a story upon.
Games like the original Marios or Sonic 2 (I mention Sonic 2 specifically but Sonics 1-3 & Knuckles would also do). Take Sonic 2, for example. It's my all time favorite game and, for some reason beyond my understanding, is the only game I know of in existence that I haven't gotten tired of or lost my child-like wonderment over yet. Not sure why. Oh, well, Smash Bros. is also one of those games, and it doesn't need a storyline despite Brawl containing one. So, Sonic 2 and Smash Bros. series for the win!