Maybe I'm weird, but does anyone else usually prefer a powerful assualt rifle to some "unique" gun that ends up either firing some kind of enemy seeking projectile or making an explosion at some point? They only ever run out of ammo...
That said, since we're counting cool powers, I submit Goldeneye: Rogue Agent. Kind of shitty PS2 game with a mix of awesome guns that had a total of one bullet and powers that you never used because Jesus, they took five minutes to regenerate.
Also, Second Sight had the best use of powers I've ever seen in a game, they were essential to the gameplay and fun.