Baldur's Gate 2 comes to mind. And rather than Metal Gear Solid 4, I think the first one deserves a mention. Policenauts by Kojima is another game with pretty good character and story development.
Overall I agree with you, most games are not at the level of good books or good movies. The story value usually fares pretty low. That's why the whole talk of "games as art" misses the point. They are never going to be seriously considered until they reach higher standards.
Yeah, Policenauts is a pretty good game. I hope you enjoy it. There are several versions available, but I believe you can get it easily for Playstation.
I agree that Half-Life was decent, too. Re: RE, I found RE2 to be especially thrilling, with the parallel stories you could explore after finishing the game once.
Overall I agree with you, most games are not at the level of good books or good movies. The story value usually fares pretty low. That's why the whole talk of "games as art" misses the point. They are never going to be seriously considered until they reach higher standards.