Certain games I feel need it to really work. Dead Space and Prototype are both such games, as they're working with this sort of inhuman reviving agent that in the former utilizes the body more like a puppet than the usual taking it over completely and in the later is all about just how horrible the infection is.
Other times, the gore can be used to show just how horrible a death would be if it happened to you, which is great for a horror theme in games (like with Bioshock, where the Big Daddies wouldn't be half as intimidating if not for how they were introduced with accompanying gore).
There are indeed some games where the gore wouldn't really matter and you could get the same visceral feeling of combat just by how things play out (consider Devil May Cry, where you'll almost never notice the blood, but you'll notice how you just backflipped off one person only to cartwheel using your sword onto two others and end with a large heavy hit). Other games, it just uses it to enhance the action (such as Gears of War).
However, stuff like God of War uses gore and blood because there's honestly no way you could not use them with a character like Kratos and how he kills. While you could make the case that there would be no need for it when fighting regular enemies, whenever a finisher comes up, there'd be no avoiding it. It was also used in the first game to give you a huge contrast between what Kratos had become compared to a normal human when you saw how enemies ripped apart the latter with relative ease every time they were introduced. It also served to help build up such a fear in a later portion of the game when enemies were targeting someone you were meant to care about, with the threat of them being turned into small giblets being a horrific conclusion you wanted to desperately avoid (similarly, this kind of thing shows up in the newest Deus Ex and is the one reason why I'm never going to get the pacifist award as I refuse to let one character that I like get torn apart just because the game doesn't give me the nonlethal ammo I need to stop a swarm of enemies from killing them). Similarly, The Darkness is a game that characterizes its main not so psychopathic lead as well as uses the apprehension it creates by using gore, so this type of approach isn't just something that works only for Kratos.
So in the end, it all comes down to how gore and blood is being used in a game. If it's not necessary to the game, then yeah, I'm all for the no gore option being in there (I actually played the first Gears game like this because it was the only way I could get my parents to let my younger brother to coop with me, as it got rid of some of the language as well, which is a completely different topic). However, some games it just can't work for.
Also, I'm unsure about the programming required for all this, but if it is actually asking for a lot to give this option, then know that I'm not advocating forcing this option to be in all games.